by Wayne Sheffield

Almost everything in a database is an object; this includes tables, constraints, views, functions, stored procedures, and data types. 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.

30-1. Changing the Name of a Database Object

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

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

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

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

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

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

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

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

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

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

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 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 30-3.

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:

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 is the object name that you want to transfer. In the above example, a table is created in the Sales schema, and it is then moved into the HumanResources schema, and finally deleted.

Problem

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

Solution

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

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 begin by checking for the existence of two databases, and dropping them if they exist. Next, I create two new databases and some new objects within them in order to demonstrate the functionality:

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

A stored procedure has been created that references a table in another database. Now if I wish to view all objects that the stored procedure depends on, I can execute the following query against sys.sql_expression_dependencies (I elaborate on the columns shortly):

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

Now create another stored procedure that references an object that doesn’t yet exist (which is an allowable scenario for a stored procedure and this 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.

This recipe 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.

Problem

You are making changes to a database object, and you need to examine all other objects that are either 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.

How It Works

The sys.dm_sql_referenced_entities and sys.dm_sql_referencing_entities Dynamic Management Functions are used to identify referenced and referencing objects. The sys.dm_sql_referenced_entities Dynamic Management Function, when provided with the referencing object name, returns a result set of objects being referenced. The sys.dm_sql_referencing_entities Dynamic Management Function, when provided the name of the object being referenced, returns a result set of objects referencing it. Notice that these two Dynamic Management Functions are named very similiarly, so ensure that you are using the proper function.

Let’s go to an example to see how these Dynamic Management Functions 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_minor_name
FROM sys.dm_sql_referenced_entities('dbo.vw_BookPublisher', 'OBJECT'),

This query returns the following result set:

referenced_entity_name referenced_minor_name
---------------------- ---------------------
BookPublisher          NULL
BookPublisher          BookPublisherID
BookPublisher          BookPublisherNM

Notice that this function shows one row for the table referenced in the view, as well as two rows 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.

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 all other objects that reference it. 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.

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:

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 DMV and examine the definition column.

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 accepts an object_id of an object, and it returns the Transact-SQL code that defines the specified object. In the above example, the object_id was obtained with the OBJECT_ID function; this function is described in the next recipe.

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 is retrieved from the sys.all_sql_modules Dynamic Management View (DMV). The view’s object_id is 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

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 accepts a schema-qualified object name, and returns the object_id for this object. This function also has 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 a 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 a null if a spatial index is specified.

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