Almost everything in a database is an object; this includes tables, constraints, views, functions, and stored procedures. Inevitably, there will come a time when you need to work on these at the object level: from renaming to moving to a different schema, to determining dependencies between objects. This chapter covers maintaining and working with database objects at the object level.

31-1. Changing the Name of Database Items

Problem

You need to change the name of an item in a database.

Solution

Utilize the system-stored procedure sp_rename to rename an item in the database as follows:

CREATE TABLE dbo.Test
       (
        Column1 INT,
        Column2 INT,
        CONSTRAINT UK_Test UNIQUE (Column1, Column2)
       );
GO
EXECUTE sp_rename 'dbo.Test', 'MyTestTable', 'object';

Executing this procedure returns the following caution message:

Caution: Changing any part of an object name could break scripts and stored procedures.

How It Works

Using the sp_rename system-stored procedure, you can rename table columns, indexes, tables, constraints, and other database items.

The syntax for sp_rename is as follows:

sp_rename [ @objname = ] 'object_name' , [ @newname = ] 'new_name'
    [ , [ @objtype = ] 'object_type' ]

The arguments of this system-stored procedure are described in Table 31-1.

This example began by creating a new table called dbo.Test, and then the system-stored procedure sp_rename was used to rename the table:

EXECUTE sp_rename 'dbo.Test', 'MyTestTable', 'object';

Notice that the first parameter used the fully qualified object name (schema.table_name), whereas the second parameter just used the new table_name. The third parameter used the object type of object.

Next, let’s change the name of Column1 to NewColumnName:

EXECUTE sp_rename 'dbo.MyTestTable.Column1', 'NewColumnName', 'column';

Executing this procedure returns the following caution message:

Caution: Changing any part of an object name could break scripts and stored procedures.

The first parameter was the schema.table_name.column_name to be renamed, and the second parameter was the new name of the column. The third parameter used the object type of column.

In this next example, we will build and then rename an index:

CREATE INDEX IX_1 ON dbo.MyTestTable (NewColumnName, Column2);
GO
EXECUTE sp_rename 'dbo.MyTestTable.IX_1', 'IX_NewIndexName', 'index';

The first parameter used the schema.table_name.index_name parameter. The second parameter used the name of the new index. The third used the object type of index.

Once you have successfully run sp_rename, you will receive the following caution message:

Caution: Changing any part of an object name could break scripts and stored procedures.

In this next example, we will create a new database and then rename it:

CREATE DATABASE TSQLRecipes;
GO
SELECT  name
FROM    sys.databases
WHERE   name IN ('TSQLRecipes', 'TSQL-Recipes'),
GO
EXECUTE sp_rename 'TSQLRecipes', 'TSQL-Recipes', 'database';
SELECT  name
FROM    sys.databases
WHERE   name IN ('TSQLRecipes', 'TSQL-Recipes'),
GO

These statements produce the following results and messages:

name
--------------
TSQLRecipes

The database name 'TSQL-Recipes' has been set.
name
--------------
TSQL-Recipes

In this example, we will build a user-defined data type and then rename it:

CREATE TYPE dbo.Age
FROM TINYINT NOT NULL;
SELECT  name
FROM    sys.types
WHERE   name IN ('Age', 'PersonAge'),
EXECUTE sp_rename 'dbo.Age', 'PersonAge', 'userdatatype';
SELECT  name
FROM    sys.types
WHERE   name IN ('Age', 'PersonAge'),

These statements produce the following results and messages:

name
---------
Age

Caution: Changing any part of an object name could break scripts and stored procedures.
name
---------
PersonAge

In this final example, we will build and then rename a stored procedure:

CREATE PROCEDURE dbo.renameMe
AS
SELECT 1;
GO
EXECUTE sp_rename 'dbo.renameMe', 'RenameMeToThis', 'OBJECT';
SELECT name FROM sys.procedures WHERE name = 'RenameMeToThis';

These statements produce the following messages and results:

Caution: Changing any part of an object name could break scripts and stored procedures.
name
--------------
RenameMeToThis

However, when changing code as shown in these examples, it is only the object name that is changed, not the underlying definition that includes the name. We can see this by examining the definition of this procedure:

SELECT  definition
FROM    sys.all_sql_modules
WHERE   object_id = OBJECT_ID('dbo.RenameMeToThis'),

This query returns:

definition
------------------------------
CREATE PROCEDURE dbo.renameMe
AS
SELECT 1;

We can see that the original name is still present.

In a real-life scenario, in conjunction with renaming an object, you’ll also want to ALTER any view, stored procedure, function, or other programmatic object that contains a reference to the original object name. I demonstrate how to find out which objects reference an object later on in this chapter in Recipe 31-3.

31-2. Changing an Object’s Schema

Problem

You need to move an object from one schema to another.

Solution

You can use the ALTER SCHEMA statement to move objects from one schema to another (this example utilizes the AdventureWorks 2014 database):

CREATE TABLE Sales.TerminationReason
       (
        TerminationReasonID INT NOT NULL
                                PRIMARY KEY,
        TerminationReasonDESC VARCHAR(100) NOT NULL
       );
GO
ALTER SCHEMA HumanResources TRANSFER Sales.TerminationReason;
GO
DROP TABLE HumanResources.TerminationReason;
GO

How It Works

The ALTER SCHEMA command takes two arguments, the first being the schema name you want to transfer the object to, and the second being the object name that you want to transfer. In the above example, a table was created in the Sales schema, then was moved into the HumanResources schema, and finally was deleted.

31-3. Identifying Object Dependencies

Problem

You need to see which objects a specified object depends upon, or which objects depend upon a specified object.

Solution

Query the sys.sql_expression_dependencies object catalog view to identify dependencies between objects.

USE master;
GO
IF DB_ID('TSQLRecipe_A') IS NOT NULL
   DROP DATABASE TSQLRecipe_A;
IF DB_ID('TSQLRecipe_B') IS NOT NULL
   DROP DATABASE TSQLRecipe_B;

-- Create two new databases
CREATE DATABASE TSQLRecipe_A;
GO
CREATE DATABASE TSQLRecipe_B;
GO

-- Create a new table in the first database
USE TSQLRecipe_A;
GO
CREATE TABLE dbo.Book
       (
        BookID INT NOT NULL
                   PRIMARY KEY,
        BookNM VARCHAR(50) NOT NULL
       );
GO

-- Create a procedure referencing an object
-- in the second database
USE TSQLRecipe_B;
GO
CREATE PROCEDURE dbo.usp_SEL_Book
AS
SELECT  BookID,
        BookNM
FROM    TSQLRecipe_A.dbo.Book;
GO

How It Works

SQL Server provides methods for identifying object dependencies within the database, across databases, and across servers (using linked server four-part names). This following example demonstrates the use of the sys.sql_expression_dependencies object catalog view to identify dependencies in several scenarios.

I began by checking for the existence of two databases, and then dropping them if they existed. Next, I create two new databases and some new objects within them in order to demonstrate the functionality.

A stored procedure was created that references a table in another database. To view all objects that the stored procedure depends on, I can execute the following query against sys.sql_expression_dependencies:

SELECT  referenced_server_name,
        referenced_database_name,
        referenced_schema_name,
        referenced_entity_name,
        is_caller_dependent
FROM    sys.sql_expression_dependencies
WHERE   OBJECT_NAME(referencing_id) = 'usp_SEL_Book';

This query returns one row (results pivoted for formatting):

referenced_server_name    NULL
referenced_database_name  TSQLRecipe_A
referenced_schema_name    dbo
referenced_entity_name    Book
is_caller_dependent       0

This demonstrates how to determine object dependencies using the sys.sql_expression_ dependencies catalog view. In the SELECT statement, five columns are referenced. The first four columns—referenced_server_name, referenced_database_name, referenced_schema_name, and referenced_entity_name—will contain the value utilized for each part of the four-part qualified name. If that particular value isn’t specified when the referencing object is created, it will be NULL. The fifth column, is_caller_dependent, indicates whether the object reference depends on the person executing the module. For example, if the object name is not fully qualified, and an object named T1 exists in two different schemas, the actual object referenced would depend on the person calling the module and the execution context.

Now, create another stored procedure that references an object that doesn’t yet exist (which is an allowable scenario for a stored procedure and which is a common practice). For example:

USE TSQLRecipe_B;
GO
CREATE PROCEDURE dbo.usp_SEL_Contract
AS
SELECT  ContractID,
        ContractNM
FROM    TSQLRecipe_A.dbo.Contract;
GO

In versions of SQL Server before SQL Server 2008, dependencies on nonexistent objects weren’t tracked. Subsequent versions corrected this behavior. You can issue the following query to check on the dependencies of usp_SEL_contract:

USE TSQLRecipe_B;
GO
SELECT  referenced_server_name,
        referenced_database_name,
        referenced_schema_name,
        referenced_entity_name,
        is_caller_dependent
FROM    sys.sql_expression_dependencies
WHERE   OBJECT_NAME(referencing_id) = 'usp_SEL_Contract';

This query returns one row (results pivoted for formatting):

referenced_server_name    NULL
referenced_database_name  TSQLRecipe_A
referenced_schema_name    dbo
referenced_entity_name    Contract
is_caller_dependent       0

Even though the object TSQLRecipe_A.dbo.Contract does not yet exist, the dependency between the referencing stored procedure and the referenced table is still represented.

31-4. Identifying Referencing and Referenced Entities

Problem

You are making changes to a database object, and you need to examine all other objects that either are referencing this object or are referenced by this object.

Solution

Utilize the sys.dm_sql_referenced_entities and sys.dm_sql_referencing_entities Dynamic Management Functions (DMFs).

How It Works

The sys.dm_sql_referenced_entities and sys.dm_sql_referencing_entities DMFs are used to identify referenced and referencing objects. The sys.dm_sql_referenced_entities DMF, when provided with the referencing object’s name, returns a result set of objects being referenced. The sys.dm_sql_referencing_entities DMF, when provided the name of the object being referenced, returns a result set of objects referencing it. Notice that these two DMFs are named very similarly, so ensure that you use the proper function.

Let’s go to an example to see how these DMFs work. This first section creates a database, and within that database a table, view, and stored procedure, where the view and stored procedure reference the table:

USE master;
GO
IF DB_ID('TSQLRecipe_A') IS NOT NULL
   DROP DATABASE TSQLRecipe_A;
GO
CREATE DATABASE TSQLRecipe_A;
GO
USE TSQLRecipe_A;
GO
CREATE TABLE dbo.BookPublisher
       (
        BookPublisherID INT NOT NULL
                            PRIMARY KEY,
        BookPublisherNM VARCHAR(30) NOT NULL
       );
GO
CREATE VIEW dbo.vw_BookPublisher
AS
SELECT  BookPublisherID,
        BookPublisherNM
FROM    dbo.BookPublisher;
GO
CREATE PROCEDURE dbo.usp_INS_BookPublisher
       @BookPublisherNM VARCHAR(30)
AS
INSERT  dbo.BookPublisher
        (BookPublisherNM)
VALUES  (@BookPublisherNM);
GO

To find all of the objects that are referenced by the dbo.vw_BookPublisher view, run the following query:

SELECT  referenced_entity_name,
        referenced_schema_name,
        referenced_minor_name
FROM    sys.dm_sql_referenced_entities('dbo.vw_BookPublisher', 'OBJECT'),

This query returns the following result set:

referenced_entity_name referenced_schema_name referenced_minor_name
---------------------- ---------------------- ---------------------
BookPublisher          dbo                    NULL
BookPublisher          dbo                    BookPublisherID
BookPublisher          dbo                    BookPublisherNM

Notice that this function shows one row for the table referenced in the view, as well as a row for each column referenced within the view.

The first parameter passed to this function is the name of the object that is referencing other objects. The second parameter designates the type of entities to list. The choices are OBJECT, DATABASE_DDL_TRIGGER, and SERVER_DDL_TRIGGER. In this case, OBJECT is the proper choice, and the result is the name of the referenced table and specific columns used in the SELECT clause of the view. The other two options will show you the objects referenced by DDL triggers at the database or server level, respectfully. For instance:

SELECT  referenced_entity_name,
        referenced_schema_name,
        referenced_minor_name
FROM    AdventureWorks2014.sys.dm_sql_referenced_entities('ddlDatabaseTriggerLog', 'DATABASE_DDL_TRIGGER'),

This query returns the following result set:

referenced_entity_name referenced_schema_name referenced_minor_name
---------------------- ---------------------- ----------------------
DatabaseLog            dbo                    NULL
DatabaseLog            dbo                    PostTime
DatabaseLog            dbo                    DatabaseUser
DatabaseLog            dbo                    Event
DatabaseLog            dbo                    Schema
DatabaseLog            dbo                    Object
DatabaseLog            dbo                    TSQL
DatabaseLog            dbo                    XmlEvent

To find all of the objects that are referencing the dbo.BookPublisher table, run the following query:

SELECT  referencing_schema_name,
        referencing_entity_name
FROM    sys.dm_sql_referencing_entities('dbo.BookPublisher', 'OBJECT'),

This query returns the following result set:

referencing_schema_name referencing_entity_name
----------------------- -----------------------
dbo                     usp_INS_BookPublisher
dbo                     vw_BookPublisher

As you can see, both the view and the stored procedure that reference the table are returned.

The first parameter passed to this function is the name of the object that you want to find references to by other objects. The second parameter designates the class of objects to list. The choices are OBJECT, TYPE, XML_SCHEMA_COLLECTION, and PARTITION FUNCTION. In this case, OBJECT is the proper choice, which results in the view and stored procedure being listed in the output. The other choices allow us to see the objects that reference a type, XML Schema Collection, or a partition function. For instance:

SELECT  referencing_schema_name,
        referencing_entity_name
FROM    AdventureWorks2014.sys.dm_sql_referencing_entities('dbo.Flag', 'TYPE'),

This query returns the following result set:

referencing_schema_name referencing_entity_name
----------------------- --------------------------
HumanResources          uspUpdateEmployeeHireInfo
HumanResources          uspUpdateEmployeeLogin

31-5. Viewing the Definition of Coded Objects

Problem

Now that you have identified the objects that are referencing an object, or that are referenced by an object, you need to view the definition of those objects.

Solution #1

Utilize the OBJECT_DEFINITION function to view the definition of an object:

USE TSQLRecipe_A;
SELECT  OBJECT_DEFINITION(OBJECT_ID('dbo.usp_INS_BookPublisher'));

This query returns the following result set:

------------------------------------------
CREATE PROCEDURE dbo.usp_INS_BookPublisher
    @BookPublisherNM varchar(30) AS
INSERT dbo.BookPublisher (BookPublisherNM)
VALUES (@BookPublisherNM);

Solution #2

Query the sys.all_sql_modules catalog view and examine the definition column:

USE TSQLRecipe_A;
SELECT  definition
FROM    sys.all_sql_modules AS asm
WHERE   object_id = OBJECT_ID('dbo.usp_INS_BookPublisher'),

This query returns the following result set:

definition
------------------------------------------
CREATE PROCEDURE dbo.usp_INS_BookPublisher
       @BookPublisherNM VARCHAR(30)
AS
INSERT  dbo.BookPublisher
        (BookPublisherNM)
VALUES  (@BookPublisherNM);

How It Works

In the first solution, the OBJECT_DEFINITION function accepted an object_id of an object, and it returned the Transact-SQL code that defines the specified object. The object_id was obtained with the OBJECT_ID function; this function is described in the next recipe. OBJECT_DEFINITION can be used to return the code from stored procedures, replication filter procedures, views, triggers, SQL functions, or rules.

The OBJECT_DEFINITION function can also be used to determine the code of system objects. For example, you can reveal the code that makes up the sys.sp_depends system-stored procedure with this query:

SELECT  OBJECT_DEFINITION(OBJECT_ID('sys.sp_depends'));

This query returns the following (abridged) result set:

create procedure sys.sp_depends  --- 1996/08/09 16:51
@objname nvarchar(776)         -- the object we want to check
as
...
select @dbname = parsename(@objname,3)

if @dbname is not null and @dbname <> db_name()
        begin
                raiserror(15250,-1,-1)
                return (1)
        end
...

In the second example, the definition of the view was retrieved from the sys.all_sql_modules Dynamic Management View (DMV). The view’s object_id was used to filter the results to just this view.

Note that if the object that you pass in is encrypted, or if you don’t have permission to view this object, you will have a NULL returned instead. This following example creates an encrypted view and then attempts to retrieve the definition with each of the above solutions:

IF OBJECT_ID('dbo.EncryptedView', 'V') IS NOT NULL
   DROP VIEW dbo.EncryptedView;
GO
CREATE VIEW dbo.EncryptedView
WITH ENCRYPTION
AS
SELECT  1 AS Result;
GO

SELECT  OBJECT_DEFINITION(OBJECT_ID('dbo.EncryptedView'));

SELECT  definition
FROM    sys.all_sql_modules AS asm
WHERE   object_id = OBJECT_ID('dbo.EncryptedView'),

These queries return the following results:

----------------------
NULL

definition
----------------------
NULL

The definitions for check and default constraints, however, are not viewable with sys.all_sql_modules. Instead, we need to query the system views sys.check_constraints and sys.default_constraints directly:

SELECT  definition
FROM    AdventureWorks2014.sys.check_constraints
WHERE   name = 'CK_WorkOrder_EndDate';

SELECT  definition
FROM    AdventureWorks2014.sys.default_constraints
WHERE   name = 'DF_ScrapReason_ModifiedDate';

These queries return the following results:

definition
---------------------------------------------
([EndDate]>=[StartDate] OR [EndDate] IS NULL)

definition
---------------------------------------------
(getdate())

You can use OBJECT_DEFINITION to get the definition for check and default constraints; however, you will need to get the object_id from the system views first:

USE AdventureWorks2014;
SELECT  name, OBJECT_DEFINITION(object_id) AS definition
FROM    sys.objects
WHERE   name IN ('CK_WorkOrder_EndDate', 'DF_ScrapReason_ModifiedDate'),

This query returns this result set:

name                         definition
---------------------------- ---------------------------------------------
CK_WorkOrder_EndDate         ([EndDate]>=[StartDate] OR [EndDate] IS NULL)
DF_ScrapReason_ModifiedDate  (getdate())

31-6. Returning a Database Object’s Name, Schema Name, and Object ID

Problem

You know an object’s name, and need to get its object_id (or you know an object’s object_id, and need to get its schema name and the name of the object).

Solution #1

Utilize the OBJECT_ID, OBJECT_NAME, and OBJECT_SCHEMA_NAME functions:

SELECT  object_id,
        OBJECT_SCHEMA_NAME(object_id) AS SchemaName,
        OBJECT_NAME(object_id) AS ObjectName
FROM    sys.tables
WHERE   object_id = OBJECT_ID('dbo.BookPublisher', 'U'),

This query returns the following result set:

object_id   SchemaName ObjectName
----------- ---------- -------------
245575913   dbo        BookPublisher

Note that you will most likely return a different object_id value.

Solution #2

Query the underlying system views directly:

SELECT  t.object_id,
        s.name AS SchemaName,
        t.name AS ObjectName
FROM    sys.tables AS t
        JOIN sys.schemas AS s
            ON t.schema_id = s.schema_id
WHERE   s.name = 'dbo'
        AND t.name = 'BookPublisher';

This query returns the same result set:

object_id   SchemaName ObjectName
----------- ---------- -------------
245575913   dbo        BookPublisher

How It Works

In Solution #1, the OBJECT_ID function accepted a schema-qualified object name, and returned the object_id for this object. This function also had an optional second parameter, which is the type of object. In the above example, the type of 'U' was specified, which is the type for a USER TABLE.

The OBJECT_NAME function accepts an object_id and returns the nonqualified name of the specified object. The OBJECT_SCHEMA_NAME function accepts an object_id and returns the name of the schema of the specified object. Both of these functions have an optional second parameter (not used in the above example), which is the database_id of the database to be searched. If the database_id is not passed in, these functions will utilize the current database.

All of these functions will return NULL if the specified object does not exist, or if the user does not have permissions on the object. Additionally, the OBJECT_ID function will return NULL if a spatial index is specified.

In Solution #2, the sys.tables and sys.schemas system views were queried directly to return the same information. This solution also provided the opportunity to perform wildcard searches with the LIKE operator.