:::: MENU ::::

Posts Categorized / System Stored Procedures

  • 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
-- Create table
Create table TableName_Old (id int identity(1,1), FName varchar(50))
-- 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'

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'

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.


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.

  • Jul 30 / 2012
  • 0
dbDigger, Monitoring and Analysis, Security and Permissions, System Stored Procedures

List members of a specific database or server role

SQL Server provides built-in database and server roles. Members of a specific role inherit the privileges of that specific role. Such implicit privileges can not be seen in security related system tables and views. We may find members of a role both by SSMS or T-SQL.

Get members of a role through SSMS

Database roles may be found under security folder of a database. While server roles may be browsed under security folder of server instance.
To view members of a database or server role, just  right click on the role in mentioned folder. There you may get list of role members.
 Get members of a role through SSMS

Get members of a role through T-SQL

More conveniently we may get list of role members both for database role or server role. Following script would list members of sysadmin server role and db_DDLAdmin database role.

-- Get members of sysadmin server role   
 EXEC sp_helpsrvrolemember 'sysadmin'   
 -- Get members of DDLAdmin database role   
 EXEC sp_helprolemember'db_DDLAdmin'  

Role name may be substituted as required in both cases. Result would be 3 column list of role members.

  • Feb 28 / 2012
  • 0
dbDigger, Monitoring and Analysis, Reference Articles Archival, SQL Server logs, System Stored Procedures, T-SQL Tips and Tricks

Reading SQL Server logs by using T-SQL

SQL Server logs are valuable way to analyze condition and activities on SQL Server. Activities may be related to logins, sessions, backups, permission errors etc. SSMS GUI provides way to read SQL Server logs however also there are powerful T-SQL alternates that provide facilities filters for dates, keywords and specific log files. Click here to read a very well written article about reading SQL Server logs through T-SQL. Also do not forget to read valuable comments under this article.

  • Dec 10 / 2011
  • 0
dbDigger, SQL Server Training, Storage, System Stored Procedures

Logical and physical file names of SQL Server data and log files

SQL Server files have two types of file names.

Logical File Name

Logical file name of SQL Server data or log file is used to refer the physical file in all Transact-SQL statements. The logical file name must comply with the rules for SQL Server identifiers and must be unique among logical file names in the database.

Physical File Name/ OS File Name

Physical file name is the name of the physical file including the directory path. It belongs to OS  hence must follow the rules for the operating system file names.

Here is a simple practical to exactly mark both the types.

Create a database

When a SQL Server database is created, by default same physical name is granted to the file in folder as that of its logical name. Following T-SQL script would create a database with different and meaningful logical and physical filenames.

( NAME = N’LogiName_data’,
FILENAME = N’F:DBs2k5PhysiName_data.mdf’)
( NAME = N’LogiName_log’,
FILENAME = N’F:DBs2k5PhysiName_log.ldf’)

A database has been created with following file names

Logical data file name      = LogiName_data
Physical data file name     = F:DBs2k5PhysiName_data.mdf
Logical log file name        = LogiName_log
Physical data file name     = F:DBs2k5PhysiName_log.ldf

We may get information about files of this database by using Files option in properties of this database in SSMS

Logical and physical file names of SQL Server data and log files

For T-SQL use following script to get files information of this database

USE [LogiPhysiFiles]

SELECT Name AS LogicalName, filename AS PhysicalFile
FROM sys.sysfiles

Following result shows both types of file names

Get SQL Server files info through T-SQL

Or use system stored procedure

USE [LogiPhysiFiles]

  • Nov 30 / 2010
  • 0
dbDigger, Logins and Users, Monitoring and Analysis, System Stored Procedures

Get SQL Server logins with same password as Login name

Standards demand that password should not be same as login name. Same standard should be implemented for SQL Server logins. So how to check that either a login on your server has same password as login name itself?
As passwords in SQL Server are stored as varbinary(128) and are not readable, so we have to use dedicated system stored procedure for this purpose. Just use following script to get SQL Server LogIns with same password as login name itself.

USE [master]

SELECT LogInName ,createdate
FROM syslogins
WHERE pwdcompare (name,PASSWORD)=1

Script would display name and creation date of any login which has same password as login name. Here in my case it returned a login which i just created with same password as logIn name.

Get SQL Server logins with same password as Login name

The system stored procedure used in above script to compare LogIn name and password is PWDCOMPARE. Click here to read more about this system stored procedure.

  • May 21 / 2009
  • 0
reinvent the wheel
DBA thoughts, DBCC Commands, dbDigger, System Functions, System Stored Procedures

Choosing between system objects and customized scripts

Most of the times i am amazed to notice that there are several commands, stored procedures and functions provided by SQL Server that may be effectively used during several tasks. We know these objects as DBCC commands, system stored procedures, DMVs, and system functions. For efficiency and effectiveness it is important for a DBA to get update of all such system provided objects. It would prevent the reinvention of wheel.

  • Click here to get list of SQL Server T-SQL system stored procedures
  • Click here to get list of SQL Srever T-SQL system functions
  • Click here to get list of SQL Srever T-SQL DBCC commands
Consult us to explore the Databases. Contact us