:::: MENU ::::

Posts Categorized / T-SQL Tips and Tricks

  • Apr 11 / 2017
  • 0
System Stored Procedures, T-SQL Tips and Tricks

Considerations while using SP_Rename to change name of object

Before going to concerned point that i want to share through this post i.e. considerations while using sp_rename,  i would like to summarize the scope and features of Sp_Rename system stored procedure.

Scope and Features of Sp_Rename

Sp_Rename is used the change the name of any user object in a SQL Server database or even the database itself. It accepts three parameters for current name, new name and object type. Generally object type parameter (@objtype) can be skipped unless there is a name conflict for concerned object.

Required Permissions

Following are the permissions required for different type of objects

Permissions for using sp_rename in SQL Server T-SQL

Issues while using sp_rename

There are some associated issues while using sp_rename. These should be known and taken care of

Using schema name in new name parameter

You can use schema name along with object name in current name of object but DO NOT use it in new name provided to SP as parameter. It would also take the schema name as object name. Consider following example for this scenario.

 

-- Use any test DB
Use Test
GO
-- Create table
Create table TableName_Old (id int identity(1,1), FName varchar(50))
GO
-- Verify Table name
select schema_name(schema_id) as SchemaName, name
from sys.objects
where type = 'U' and name like 'TableName_Old'

-- Rename table
exec sp_rename 'dbo.TableName_Old', 'dbo.TableName_New'
GO

And when we tried to verify with table with new name the result is empty.

-- Verify table with new name
select schema_name(schema_id) as SchemaName, name
from sys.objects
where type = 'U' and name like 'TableName_new'

Let us run a generic search to include more tables.

-- Verify table with generic search
select schema_name(schema_id) as SchemaName, name
from sys.objects
where type = 'U' and name like '%TableName%'

Now we have following result that shows that schema dbo is also prefixed in name of table.
Result of sp_rename T-SQL

So we have to execute another sp_rename command to set the required name of this table.


-- Rename and remove the schema prefix
exec sp_rename 'dbo.[dbo.TableName_New]', 'TableName_New'
GO

This fixed out the issue and we have table name as TableName_New.

SP_rename for name correction T-SQL

Effect on Associated Indexes

Associated index will be automatically renamed whenever a PRIMARY KEY or UNIQUE constraint is renamed.
In same way if a renamed index is associated to a PRIMARY KEY constraint,
the PRIMARY KEY constraint will be automatically renamed.

References to Object

Renaming an object will not update its references to that object. This task should be done manually to avoid code failure after rename.

Code definition

Renaming a stored procedure, function, view, or trigger will not change the name of the corresponding object name in the definition column of the sys.sql_modules catalog view.

Conclusion

Sp_rename should be avoided if you have option to drop and recreate an object with a different name. Analysis of code base is imperative to make sure that code will not break after rename is performed.

  • Oct 01 / 2014
  • 0
dbDigger, T-SQL Scripts, T-SQL Tips and Tricks

Increase or shrink file size for SQL Server database files

Following handy scripts help to manage the SQL Server database files. You may increase the max size and current size as well.

-- Increase file max size  
 USE [master];  
 ALTER DATABASE DatabaseNameHere MODIFY FILE ( NAME = N'FileNameHere', maxsIZE = 1500MB)  
 GO  
 -- Increase file size  
 USE [master];  
 ALTER DATABASE DatabaseNameHere MODIFY FILE ( NAME = N'FileNameHere', sIZE = 1500MB)  
 GO  

Shrink operation may be up to a specific extent or just shrink by removing the space available ay the end of file without manipulating data in file.If file shrink operation is cancelled during execution the current status is not rolled back. TRUNCATEONLY operation is fast.

-- Shrink to specific limit  
 USE DatabaseNameHere;  
 DBCC SHRINKFILE (N'FileNameHere' , 200)  
 GO  
 -- Shrink to space available at the end of file  
 USE DatabaseNameHere;  
 DBCC SHRINKFILE (N'FileNameHere' , 0, TRUNCATEONLY)  
 GO  
  • Oct 01 / 2014
  • 0
dbDigger, T-SQL Scripts, T-SQL Tips and Tricks

Get all file groups and file details in a SQL Server database

Following script will provide information about the filegroups and files in a given database. It includes names, location, allocated size, used size and percent free.

-- get data file space and locations  
 Use DatabaseNameHere;  
 SELECT b.groupname AS 'File Group'  
   ,a.NAME  
   ,physical_name  
   ,CONVERT(INT, a.Size / 128.000, 2) AS [Currently Allocated Space (MB)]  
   ,CONVERT(INT, FILEPROPERTY(a.NAME, 'SpaceUsed') / 128.000, 2) AS [Space Used (MB)]  
   ,CONVERT(INT, a.max_Size / 128.000, 2) [Maximum Space (MB)]  
   ,CASE   
     WHEN a.IS_PERCENT_GROWTH = 0  
       THEN CONVERT(VARCHAR, CONVERT(DECIMAL(15, 2), ROUND(a.growth / 128.000, 2))) + ' MB'  
     ELSE CONVERT(VARCHAR, a.growth) + ' PERCENT'  
     END [Growth]  
   ,CONVERT(INT, (a.Size - FILEPROPERTY(a.NAME, 'SpaceUsed')) / 128.000, 2) AS [Available Space (MB)]  
   ,(CONVERT(INT, ROUND((a.Size - FILEPROPERTY(a.NAME, 'SpaceUsed')) / 128.000, 2)) * 100) / (CONVERT(INT, ROUND(a.Size / 128.000, 2))) AS PercentFree  
 FROM sys.database_files a(NOLOCK)  
 LEFT OUTER JOIN sysfilegroups b(NOLOCK) ON a.data_space_id = b.groupid  
 ORDER BY PercentFree  
  • Oct 11 / 2013
  • 0
Backup and Restore, Consultancy, dbDigger, T-SQL Tips and Tricks

Create directories through T-SQL

In a recent task i was connected to SQL Server through SSMS and had no OS access. To configure the automatic backups i was required to create couple of directories on the disk. So without having OS access i had to create directories by using T-SQL. Following is the command that i used to create ‘D:DBBackupsDBDigger’

EXEC master.dbo.xp_create_subdir 'D:DBBackups_DBDIgger'
GO
  • Aug 29 / 2013
  • 0
Data Modeling and Database Design, dbDigger, Security and Permissions, T-SQL Interview Questions, T-SQL Tips and Tricks

Drawbacks of using Dynamic SQL

I am performing some knowledge discovery that what may be the bad effects of using dynamic SQL in SQL Server environment. So while going through some good articles on it i have figured out following major issues/side effects of dynamic SQL

  • Query plans of Dynamic SQL are not cached so not reused
  • Dynamic SQL can put you vulnerable for SQL injection attack
  • Messy quotation marks and  spacing complicate the query writing
  • Network traffic is increased as compared to a USP executed
  • Generating the dependencies through various methods becomes unreliable as objects used in dynamic SQL can not be traced by system views
  • Ownership chaining is skipped hence permissions are compromised

These major reasons are enough to think twice before using the dynamic SQL. So use it wisely and also look for options to avoid it.

  • Apr 11 / 2013
  • 0
dbDigger, ETL, T-SQL Interview Questions, T-SQL Tips and Tricks

Using TOP in DELETE statement for T-SQL

Suppose we have a WHILE loop for deleting specified amount of data batches in each loop. Simple solution is to use TOP operator in loop for specifying the number of rows to be deleted in each batch. But it generates error while trying to use TOP operator in delete operation. Let us have a look at the problem.

-- Create a table with only one column in it  
 CREATE TABLE DemoTable (id SMALLINT identity(1, 1))  
 GO  
 -- Insert 100 values in identity column  
 INSERT INTO DemoTable DEFAULT VALUES   
 GO 100  
 -- Verify the values   
 SELECT * FROM DemoTable  
 GO  
 -- Try to delete statement for top 10 rows  
 DELETE TOP 10 FROM DemoTable  

but following error message is generated

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near ’10’.

It looks that TOP operator is not working in the DELETE statement. So can we use the TOP operator in DELETE operations or not?

Answer is YES, we may use TOP operator in DELETE operations but with little modification. Correct statement is as under with brackets around the number.

 -- Using TOP in delete statement  
 DELETE TOP (10) FROM DemoTable  

So we have deleted the 10 records from demo table by using TOP operator in DELETE statement. Similarly instead of constant number any variable may be used in brackets with TOP.

 -- Drop DemoTable  
 DROP TABLE DemoTable  

However it is notable that according to BOL
“TOP cannot be used together with UPDATE and DELETE statements on partitioned views.”.

Consult us to explore the Databases. Contact us