Retrieving SQL Server Metadata with Help of T-SQL

Total: 3 Average: 4
This article contains a list of scripts that mine the SQL Server metadata in the various system functions, stored procedures, tables, and catalog views. Metadata queries are really helpful in discovering information for a given database schema. You can copy all the T-SQL scripts that are listed in this article and use for your own purposes. However, please do some tests before apply to a production database.

1 Server Information
1.1 Basic Server Information
1.2 Linked Servers
1.3 Get List of Databases from SQL Server
1.4 Last Backup
1.5 Active User Connections

2 Database Information
2.1 Database File Location
2.2 Tables
2.2.1 The Number of Records in a Table
2.2.2 Use the Clustered Index to Getting Row Counts
2.2.3 Finding Heaps (tables without clustered index)
2.2.4 Table Activity
2.3 Views
2.4 Synonyms
2.5 Stored Procedures
2.6 Functions
2.7 Triggers
2.8 Check Constraints

3 Drill Down Into the Data Model
3.1 Columns
3.1.1 Column Defaults
3.1.2 Computed Columns
3.1.3 Identity Columns
3.2 Keys and Indexes
3.2.1 Foreign Keys
3.2.2 Missing Indexes that support Foreign Keys
3.3 Object Dependencies
3.3.1 Query the System Catalog Views
3.3.2 Using a Common Table Expression

1. Server Information

1.1 Basic Server Information

The @@ functions provide basic server information.

-- Server and instance name
Select @@SERVERNAME as [Server\Instance];
-- SQL Server Version
Select @@VERSION as SQLServerVersion;
-- SQL Server Instance
Select @@ServiceName AS ServiceInstance;
 
-- Current Database
Select DB_NAME() AS CurrentDB_Name;

To get the information about how long has your server been running since the last SQL Server startup?

SELECT  @@Servername AS ServerName ,
        create_date AS ServerStarted ,
        DATEDIFF(s, create_date, GETDATE()) / 86400.0 AS DaysRunning ,
        DATEDIFF(s, create_date, GETDATE()) AS SecondsRunnig
FROM    sys.databases
WHERE   name = 'tempdb';
GO

The tempdb system database is recreated every time the server restarts.

1.2 Linked Servers

A linked server enables the SQL Server Database Engine to execute commands against OLE DB data sources outside of the instance of SQL Server. The following query checks whether your server linked to other servers.

EXEC sp_helpserver;
--OR
EXEC sp_linkedservers;
--OR
SELECT  @@SERVERNAME AS Server ,
        Server_Id AS LinkedServerID ,
        name AS LinkedServer ,
        Product ,
        Provider ,
        Data_Source ,
        Modify_Date
FROM    sys.servers
ORDER BY name;
GO

Linked servers offer the following advantages:

  • The ability to access data from outside of SQL Server.
  • The ability to issue distributed queries, updates, commands, and transactions on heterogeneous data sources across the enterprise.
  • The ability to address diverse data sources similarly.

1.3 Get list of databases from SQL Server

There are several ways to get the list of all server databases.

EXEC sp_helpdb;
--OR
EXEC sp_Databases;
--OR
SELECT  @@SERVERNAME AS Server ,
        name AS DBName ,
        recovery_model_Desc AS RecoveryModel ,
        Compatibility_level AS CompatiblityLevel ,
        create_date ,
        state_desc
FROM    sys.databases
ORDER BY Name;
--OR
SELECT  @@SERVERNAME AS Server ,
        d.name AS DBName ,
        create_date ,
        compatibility_level ,
        m.physical_name AS FileName
FROM    sys.databases d
        JOIN sys.master_files m ON d.database_id = m.database_id
WHERE   m.[type] = 0 -- data files only
ORDER BY d.name;
GO

If the caller of sys.databases is not the owner of the database and the database is not master or tempdb, the minimum permissions required to see the corresponding row are ALTER ANY DATABASE or VIEW ANY DATABASE server-level permission, or CREATE DATABASE permission in the master database. The database to which the caller is connected can always be viewed in sys.databases.

1.4 Last Backup

The following query returns the information about the most recent backup.

SELECT  @@Servername AS ServerName ,
        d.Name AS DBName ,
        MAX(b.backup_finish_date) AS LastBackupCompleted
FROM    sys.databases d
        LEFT OUTER JOIN msdb..backupset b
                    ON b.database_name = d.name
                       AND b.[type] = 'D'
GROUP BY d.Name
ORDER BY d.Name;

Next query provides you a location of the latest backup.

SELECT  @@Servername AS ServerName ,
        d.Name AS DBName ,
        b.Backup_finish_date ,
        bmf.Physical_Device_name
FROM    sys.databases d
        INNER JOIN msdb..backupset b ON b.database_name = d.name
                                        AND b.[type] = 'D'
        INNER JOIN msdb.dbo.backupmediafamily bmf
                   ON b.media_set_id = bmf.media_set_id
ORDER BY d.NAME ,
        b.Backup_finish_date DESC;
GO

If you use SQL Server Management Studio, you can right-click a database, and then select Properties. The last backup date should be the first field in the dialog.

1.5 Active User Connections

The following query is legal only for SQL Server 2012 and higher. In the previous editions, the database id column was absent in DMV sys.dm_exec_sessions.

SELECT  @@Servername AS Server ,
        DB_NAME(database_id) AS DatabaseName ,
        COUNT(database_id) AS Connections ,
        Login_name AS LoginName ,
        MIN(Login_Time) AS Login_Time ,
        MIN(COALESCE(last_request_end_time, last_request_start_time))
                                                         AS Last_Batch
FROM    sys.dm_exec_sessions
WHERE   database_id > 0
        AND DB_NAME(database_id) NOT IN ( 'master', 'msdb' )
GROUP BY database_id ,
         login_name
ORDER BY DatabaseName;

To find out which DATABASE is being accessed by users, you can use sp_who.

2 Database Information

If you need to get information about the objects in each of our databases, you can use various catalog views and Dynamic management Views. The sys.objects system table is one of the key tables for gathering information about the objects.

-- In this example U is for tables.
-- Try swapping in one of the many other types.
USE MyDatabase;
GO
SELECT  *
FROM    sys.objects
WHERE   type = 'U';

Below is the list of objects types on which we can filter (see also the sys.objects documentation on Microsoft’s MSDN website).

AF = Aggregate function (CLR)
P = SQL Stored Procedure
TA = Assembly (CLR) DML trigger
C = CHECK constraint
PC = Assembly (CLR) stored procedure
TF = SQL table-valued-function
D = DEFAULT (constraint or stand-alone)
PG = Plan guide
TR = SQL DML trigger
F = FOREIGN KEY constraint
PK = PRIMARY KEY constraint
TT = Table type
FN = SQL scalar function
R = Rule (old-style, stand-alone)
U = Table (user-defined)
FS = Assembly (CLR) scalar-function
RF = Replication-filter-procedure
UQ = UNIQUE constraint
FT = Assembly (CLR) table-valued function
S = System base table
V = View
IF = SQL inline table-valued function
SN = Synonym
X = Extended stored procedure
IT = Internal table
SQ = Service queue

You can use the OBJECTPROPERTY metadata function and sys.tables and sys.views to get the objects info that makes up our database schemas.

2.1 Database File Location

You can use the following queries to get a physical location of the main DB file (*.mdf) and the log file (*.ldf)

EXEC sp_Helpfile;
--OR
SELECT  @@Servername AS Server ,
        DB_NAME() AS DB_Name ,
        File_id ,
        Type_desc ,
        Name ,
        LEFT(Physical_Name, 1) AS Drive ,
        Physical_Name ,
        RIGHT(physical_name, 3) AS Ext ,
        Size ,
        Growth
FROM    sys.database_files
ORDER BY File_id;
GO

sys.database_files contains a row per file of a database as stored in the database itself.

2.2 Tables

Compared to GUI, scripts provide you with more flexibility while getting information about tables. INFORMATION_SCHEMA tables are based on ANSI standard, however, these tables do not provide an information about objects that are not the part of the standard (e.g. triggers and extended properties). You can use Catalog Views that return information that is used by the SQL Server Database Engine. It is recommended to use catalog views because they are the most general interface to the catalog metadata and provide the most efficient way to obtain, transform, and present customized forms of this information.

EXEC sp_tables; -- Note this method returns both table and views.
--OR
SELECT  @@Servername AS ServerName ,
        TABLE_CATALOG ,
        TABLE_SCHEMA ,
        TABLE_NAME
FROM    INFORMATION_SCHEMA.TABLES
WHERE   TABLE_TYPE = 'BASE TABLE'
ORDER BY TABLE_NAME ;
--OR
SELECT  @@Servername AS ServerName ,
        DB_NAME() AS DBName ,
        o.name AS 'TableName' ,
        o.[Type] ,
        o.create_date
FROM    sys.objects o
WHERE   o.Type = 'U' -- User table
ORDER BY o.name;
--OR
SELECT  @@Servername AS ServerName ,
        DB_NAME() AS DBName ,
        t.Name AS TableName,
        t.[Type],
        t.create_date
FROM    sys.tables t
ORDER BY t.Name;
GO

All user-available catalog metadata is exposed through catalog views.

2.2.1 The Number of Records in a Table

In SSMS, you can right click a required table and then click Table Properties. The Table Properties window provides an information about the number of records in a table. But what if you need to get this information for hundreds of tables? One way is to use SELECT COUNT(*) FROM TABLENAME, for every single table. Another way is to use T-SQL to generate a set of statements to return the row count for each table in the current database.

SELECT  'Select ''' + DB_NAME() + '.' + SCHEMA_NAME(SCHEMA_ID) + '.'
        + LEFT(o.name, 128) + ''' as DBName, count(*) as Count From ' +
        o.name + ';' AS ' Script generator to get counts for all tables'
FROM    sys.objects o
WHERE   o.[type] = 'U'
ORDER BY o.name;
GO

In case the previous query doesn’t work, try to add a schema name before tables.

SELECT  'Select ''' + DB_NAME() + '.' + SCHEMA_NAME(SCHEMA_ID) + '.'
        + LEFT(o.name, 128) + ''' as DBName, count(*) as Count From '
        + SCHEMA_NAME(SCHEMA_ID) + '.' + o.name
        + ';' AS ' Script generator to get counts for all tables'
FROM    sys.objects o
WHERE   o.[type] = 'U'
ORDER BY o.name;

sp_msForEachTable

sp_msforeachtable is non-documented Microsoft function.
The function loops through all the tables in a database executing a query, and replacing ‘?’ with each table name. sp_msforeachdb is a similar database function.
Note that these functions do not handle, some special characters in object names. For instance, the dash “-” symbol in the table name will lead to fail.

2.2.2 Use the Clustered Index to Getting Row Counts

All previous methods use COUNT(*). If a table contains 500K records, this method works quite slow. The fastest way to get row counts is to get the record counts from the clustered index or heap partition.

SELECT  @@ServerName AS Server ,
        DB_NAME() AS DBName ,
        OBJECT_SCHEMA_NAME(p.object_id) AS SchemaName ,
        OBJECT_NAME(p.object_id) AS TableName ,
        i.Type_Desc ,
        i.Name AS IndexUsedForCounts ,
        SUM(p.Rows) AS Rows
FROM    sys.partitions p
        JOIN sys.indexes i ON i.object_id = p.object_id
                              AND i.index_id = p.index_id
WHERE   i.type_desc IN ( 'CLUSTERED', 'HEAP' )
                             -- This is key (1 index per table)
        AND OBJECT_SCHEMA_NAME(p.object_id) <> 'sys'
GROUP BY p.object_id ,
        i.type_desc ,
        i.Name
ORDER BY SchemaName ,
        TableName;
 
-- OR
-- Similar method that uses DMV dm_db_partition_stats
SELECT  @@ServerName AS ServerName ,
        DB_NAME() AS DBName ,
        OBJECT_SCHEMA_NAME(ddps.object_id) AS SchemaName ,
        OBJECT_NAME(ddps.object_id) AS TableName ,
        i.Type_Desc ,
        i.Name AS IndexUsedForCounts ,
        SUM(ddps.row_count) AS Rows
FROM    sys.dm_db_partition_stats ddps
        JOIN sys.indexes i ON i.object_id = ddps.object_id
                              AND i.index_id = ddps.index_id
WHERE   i.type_desc IN ( 'CLUSTERED', 'HEAP' )
                              -- This is key (1 index per table)
        AND OBJECT_SCHEMA_NAME(ddps.object_id) <> 'sys'
GROUP BY ddps.object_id ,
        i.type_desc ,
        i.Name
ORDER BY SchemaName ,
        TableName;
GO

Microsoft states that the record count updates on indexes may not always match the record counts of the table. It happens due to a delay in the index counts. In most cases they are exactly the same or close and will be the same shortly.

2.2.3 Finding Heaps (tables without clustered index)

A heap is a table without a clustered index. One or more nonclustered indexes can be created on tables stored as a heap. Data is stored in the heap without specifying an order. Usually, data is initially stored in the order in which is the rows are inserted into the table, but the Database Engine can move data around in the heap to store the rows efficiently; so the data order cannot be predicted. To guarantee the order of rows returned from a heap, you must use the ORDER BY clause. To specify the order for storage of the rows, create a clustered index on the table so that the table is not a heap.

-- Method 1 --
SELECT  @@Servername AS ServerName ,
        DB_NAME() AS DBName ,
        t.Name AS HeapTable ,
        t.Create_Date
FROM    sys.tables t
        INNER JOIN sys.indexes i ON t.object_id = i.object_id
                                    AND i.type_desc = 'HEAP'
ORDER BY t.Name
 
-- Method 2--
SELECT  @@Servername AS ServerName ,
        DB_NAME() AS DBName ,
        t.Name AS HeapTable ,
        t.Create_Date
FROM    sys.tables t
WHERE   OBJECTPROPERTY(OBJECT_ID, 'TableHasClustIndex') = 0
ORDER BY t.Name;
 
-- Method 3--
SELECT  @@ServerName AS Server ,
        DB_NAME() AS DBName ,
        OBJECT_SCHEMA_NAME(ddps.object_id) AS SchemaName ,
        OBJECT_NAME(ddps.object_id) AS TableName ,
        i.Type_Desc ,
        SUM(ddps.row_count) AS Rows
FROM    sys.dm_db_partition_stats AS ddps
        JOIN sys.indexes i ON i.object_id = ddps.object_id
                              AND i.index_id = ddps.index_id
WHERE   i.type_desc = 'HEAP'
        AND OBJECT_SCHEMA_NAME(ddps.object_id) <> 'sys'
GROUP BY ddps.object_id ,
        i.type_desc
ORDER BY TableName;

When to use a heap:
If a table is a heap and does not have any nonclustered indexes, then the entire table must be examined (a table scan) to find any row. This can be acceptable when the table is tiny, such as a list of the 12 regional offices of a company.

Do not use a heap when:
The data is frequently returned in a sorted order. A clustered index on the sorting column could avoid the sorting operation. Also, do not use a heap when the data is frequently grouped together. Data must be sorted before it is grouped, and a clustered index on the sorting column could avoid the sorting operation.

2.2.4 Table Activity

While doing some optimizations, it is important to understand what tables have the most reads and writes. Remember that the statistics from Dynamic Management Views are deleted every time SQL Server restarts.

-- Table Reads and Writes
SELECT  @@ServerName AS ServerName ,
        DB_NAME() AS DBName ,
        OBJECT_NAME(ddius.object_id) AS TableName ,
        SUM(ddius.user_seeks + ddius.user_scans + ddius.user_lookups)
                                                            AS Reads,
        SUM(ddius.user_updates) AS Writes ,
        SUM(ddius.user_seeks + ddius.user_scans + ddius.user_lookups
            + ddius.user_updates) AS [Reads&Writes] ,
        ( SELECT    DATEDIFF(s, create_date, GETDATE()) / 86400.0
          FROM      master.sys.databases
          WHERE     name = 'tempdb'
        ) AS SampleDays ,
        ( SELECT    DATEDIFF(s, create_date, GETDATE())
                                                  AS SecoundsRunnig
          FROM      master.sys.databases
          WHERE     name = 'tempdb'
        ) AS SampleSeconds
FROM    sys.dm_db_index_usage_stats ddius
        INNER JOIN sys.indexes i ON ddius.object_id = i.object_id
                                     AND i.index_id = ddius.index_id
WHERE   OBJECTPROPERTY(ddius.object_id, 'IsUserTable') = 1
        AND ddius.database_id = DB_ID()
GROUP BY OBJECT_NAME(ddius.object_id)
ORDER BY [Reads&Writes] DESC;
GO

Another version of this query uses a cursor to combine the information for all Tables and for all databases on the server. Although cursors have slow performance, navigating multiple databases is a good use for one.

-- Table Reads and Writes
DECLARE DBNameCursor CURSOR
FOR
    SELECT  Name
    FROM    sys.databases
    WHERE   Name NOT IN ( 'master', 'model', 'msdb', 'tempdb',
                            'distribution' )
    ORDER BY Name;
DECLARE @DBName NVARCHAR(128)
DECLARE @cmd VARCHAR(4000)
IF OBJECT_ID(N'tempdb..TempResults') IS NOT NULL
    BEGIN
        DROP TABLE tempdb..TempResults
    END
CREATE TABLE tempdb..TempResults
    (
      ServerName NVARCHAR(128) ,
      DBName NVARCHAR(128) ,
      TableName NVARCHAR(128) ,
      Reads INT ,
      Writes INT ,
      ReadsWrites INT ,
      SampleDays DECIMAL(18, 8) ,
      SampleSeconds INT
    )
OPEN DBNameCursor
FETCH NEXT FROM DBNameCursor INTO @DBName
WHILE @@fetch_status = 0
    BEGIN
----------------------------------------------------
-- Print @DBName
        SELECT  @cmd = 'Use ' + @DBName + '; '
        SELECT  @cmd = @cmd + ' Insert Into tempdb..TempResults
SELECT @@ServerName AS ServerName,
DB_NAME() AS DBName,
object_name(ddius.object_id) AS TableName ,
SUM(ddius.user_seeks
+ ddius.user_scans
+ ddius.user_lookups) AS Reads,
SUM(ddius.user_updates) as Writes,
SUM(ddius.user_seeks
+ ddius.user_scans
+ ddius.user_lookups
+ ddius.user_updates) as ReadsWrites,
(SELECT datediff(s,create_date, GETDATE()) / 86400.0
FROM sys.databases WHERE name = ''tempdb'') AS SampleDays,
(SELECT datediff(s,create_date, GETDATE())
FROM sys.databases WHERE name = ''tempdb'') as SampleSeconds
FROM sys.dm_db_index_usage_stats ddius
INNER JOIN sys.indexes i
ON ddius.object_id = i.object_id
AND i.index_id = ddius.index_id
WHERE objectproperty(ddius.object_id,''IsUserTable'') = 1 --True
AND ddius.database_id = db_id()
GROUP BY object_name(ddius.object_id)
ORDER BY ReadsWrites DESC;'
--PRINT @cmd
        EXECUTE (@cmd)
-----------------------------------------------------
        FETCH NEXT FROM DBNameCursor INTO @DBName
    END
CLOSE DBNameCursor
DEALLOCATE DBNameCursor
SELECT  *
FROM    tempdb..TempResults
ORDER BY DBName ,
        TableName;
--DROP TABLE tempdb..TempResults;

2.3 Views

You can use SELECT DISTINCT in the view definition to make the view READ ONLY.
A view is only updateable in the case when each row in the view maps identically to a single row in the underlying table. A view that fails these criteria, such as any view built on more than one table, or that uses grouping, aggregations, and calculations in its definition will be read only.

SELECT  @@Servername AS ServerName ,
        DB_NAME() AS DBName ,
        o.name AS ViewName ,
        o.[Type] ,
        o.create_date
FROM    sys.objects o
WHERE   o.[Type] = 'V' -- View
ORDER BY o.NAME
 
--OR
SELECT  @@Servername AS ServerName ,
        DB_NAME() AS DBName ,
        Name AS ViewName ,
        create_date
FROM    sys.Views
ORDER BY Name
--OR
SELECT  @@Servername AS ServerName ,
        TABLE_CATALOG ,
        TABLE_SCHEMA ,
        TABLE_NAME ,
        TABLE_TYPE
FROM    INFORMATION_SCHEMA.TABLES
WHERE   TABLE_TYPE = 'VIEW'
ORDER BY TABLE_NAME
--OR
-- View details (Show the CREATE VIEW Code)
SELECT  @@Servername AS ServerName ,
        DB_NAME() AS DB_Name ,
        o.name AS 'ViewName' ,
        o.Type ,
        o.create_date ,
        sm.[DEFINITION] AS 'View script'
FROM    sys.objects o
        INNER JOIN sys.sql_modules sm ON o.object_id = sm.OBJECT_ID
WHERE   o.Type = 'V' -- View
ORDER BY o.NAME;
GO

2.4 Synonyms

-- which synonyms exist?
SELECT  @@Servername AS ServerName ,
        DB_NAME() AS DBName ,
        o.name AS ViewName ,
        o.Type ,
        o.create_date
FROM    sys.objects o
WHERE   o.[Type] = 'SN' -- Synonym
ORDER BY o.NAME;
--OR
-- synonymn details
SELECT  @@Servername AS ServerName ,
        DB_NAME() AS DBName ,
        s.name AS synonyms ,
        s.create_date ,
        s.base_object_name
FROM    sys.synonyms s
ORDER BY s.name;
GO

2.5 Stored Procedures

You can use the catalog views to find out which stored procedures exist, what activity they perform, and which tables they reference.

-- Stored Procedures
SELECT  @@Servername AS ServerName ,
        DB_NAME() AS DBName ,
        o.name AS StoredProcedureName ,
        o.[Type] ,
        o.create_date
FROM    sys.objects o
WHERE   o.[Type] = 'P' -- Stored Procedures
ORDER BY o.name
--OR
-- Stored Procedure details
SELECT  @@Servername AS ServerName ,
        DB_NAME() AS DB_Name ,
        o.name AS 'ViewName' ,
        o.[type] ,
        o.Create_date ,
        sm.[definition] AS 'Stored Procedure script'
FROM    sys.objects o
        INNER JOIN sys.sql_modules sm ON o.object_id = sm.object_id
WHERE   o.[type] = 'P' -- Stored Procedures
        -- AND sm.[definition] LIKE '%insert%'
        -- AND sm.[definition] LIKE '%update%'
        -- AND sm.[definition] LIKE '%delete%'
        -- AND sm.[definition] LIKE '%tablename%'
ORDER BY o.name;
GO

With a simple addition to the WHERE clause of the stored procedure details query, you can view, for example, only those stored procedures that perform inserts.

…
WHERE   o.[type]  = 'P' -- Stored Procedures
        AND sm.definition LIKE '%insert%'
ORDER BY o.name
…

Simply modify the WHERE clause as required to investigate stored procedures that do updates (LIKE ‘%update%’), deletes (LIKE ‘%delete%’), or reference a particular table (LIKE ‘%tablename%’).

2.6 Functions

-- Functions
SELECT  @@Servername AS ServerName ,
        DB_NAME() AS DB_Name ,
        o.name AS 'Functions' ,
        o.[Type] ,
        o.create_date
FROM    sys.objects o
WHERE   o.Type = 'FN' -- Function
ORDER BY o.NAME;
--OR
-- Function details
SELECT  @@Servername AS ServerName ,
        DB_NAME() AS DB_Name ,
        o.name AS 'FunctionName' ,
        o.[type] ,
        o.create_date ,
        sm.[DEFINITION] AS 'Function script'
FROM    sys.objects o
        INNER JOIN sys.sql_modules sm ON o.object_id = sm.OBJECT_ID
WHERE   o.[Type] = 'FN' -- Function
ORDER BY o.NAME;
GO

2.7 Triggers

DML triggers is a special type of stored procedure that automatically takes effect when a data manipulation language (DML) event takes place that affects the table or view defined in the trigger. DML events include INSERT, UPDATE, or DELETE statements. DML triggers can be used to enforce business rules and data integrity, query other tables, and include complex Transact-SQL statements. The trigger and the statement that fires it are treated as a single transaction, which can be rolled back from within the trigger. If a severe error is detected (for example, insufficient disk space), the entire transaction automatically rolls back.

DDL triggers fire in response to a variety of Data Definition Language (DDL) events. These events primarily correspond to Transact-SQL statements that start with the keywords CREATE, ALTER, DROP, GRANT, DENY, REVOKE or UPDATE STATISTICS. Certain system stored procedures that perform DDL-like operations can also fire DDL triggers.
Use DDL triggers when you want to do the following:

  • Prevent certain changes to your database schema.
  • Have something occur in the database in response to a change in your database schema.
  • Record changes or events in the database schema.
-- Table Triggers
SELECT  @@Servername AS ServerName ,
        DB_NAME() AS DBName ,
        parent.name AS TableName ,
        o.name AS TriggerName ,
        o.[Type] ,
        o.create_date
FROM    sys.objects o
        INNER JOIN sys.objects parent ON o.parent_object_id =
                                                   parent.object_id
WHERE   o.Type = 'TR' -- Triggers
ORDER BY parent.name ,
        o.NAME
--OR
SELECT  @@Servername AS ServerName ,
        DB_NAME() AS DB_Name ,
        Parent_id ,
        name AS TriggerName ,
        create_date
FROM    sys.triggers
WHERE   parent_class = 1
ORDER BY name;
 
-- Trigger Details
SELECT  @@Servername AS ServerName ,
        DB_NAME() AS DB_Name ,
        OBJECT_NAME(Parent_object_id) AS TableName ,
        o.name AS 'TriggerName' ,
        o.Type ,
        o.create_date ,
        sm.[DEFINITION] AS 'Trigger script'
FROM    sys.objects o
        INNER JOIN sys.sql_modules sm ON o.object_id = sm.OBJECT_ID
WHERE   o.Type = 'TR' -- Triggers
ORDER BY o.NAME;
GO

2.8 Check Constraints

Constraints are rules that the SQL Server Database Engine provides for you. CHECK constraints enforce domain integrity by limiting the values that are accepted by one or more columns. You can create a CHECK constraint with any logical (Boolean) expression that returns TRUE or FALSE based on the logical operators.

- Check Constraints
SELECT  @@Servername AS ServerName ,
        DB_NAME() AS DBName ,
        parent.name AS 'TableName' ,
        o.name AS 'Constraints' ,
        o.[Type] ,
        o.create_date
FROM    sys.objects o
        INNER JOIN sys.objects parent
               ON o.parent_object_id = parent.object_id
WHERE   o.Type = 'C' -- Check Constraints
ORDER BY parent.name ,
        o.name
 
--CHECK constriant definitions
SELECT  @@Servername AS ServerName ,
        DB_NAME() AS DBName ,
        OBJECT_SCHEMA_NAME(parent_object_id) AS SchemaName ,
        OBJECT_NAME(parent_object_id) AS TableName ,
        parent_column_id AS Column_NBR ,
        Name AS CheckConstraintName ,
        type ,
        type_desc ,
        create_date ,
        OBJECT_DEFINITION(object_id) AS CheckConstraintDefinition
FROM    sys.Check_constraints
ORDER BY TableName ,
        SchemaName ,
        Column_NBR
GO

CHECK constraints are a good way to implement business logic in a database.

3 Drill Down Into the Data Model

The above scripts gave us an idea about the “top level” objects in our database. Sometimes we need to get more information on the table, including columns and their data types, default values, keys, and indexes.
The following queries provide the “reverse engineering” of an existing data model.

3.1 Columns

The following script gets the tables and columns from the entire database. You can copy the query result into an Excel file, and then set up filters and sorting, and get acquainted with the data types used in the database.

-- Table Columns
SELECT  @@Servername AS Server ,
        DB_NAME() AS DBName ,
        isc.Table_Name AS TableName ,
        isc.Table_Schema AS SchemaName ,
        Ordinal_Position AS Ord ,
        Column_Name ,
        Data_Type ,
        Numeric_Precision AS Prec ,
        Numeric_Scale AS Scale ,
-- -1 means MAX like Varchar(MAX)
        Character_Maximum_Length AS LEN ,
        Is_Nullable ,
        Column_Default ,
        Table_Type
FROM    INFORMATION_SCHEMA.COLUMNS isc
        INNER JOIN information_schema.tables ist
              ON isc.table_name = ist.table_name
--      WHERE Table_Type = 'BASE TABLE' -- 'Base Table' or 'View'
ORDER BY DBName ,
        TableName ,
        SchemaName ,
        Ordinal_position;
 
-- Summary of Column names and usage counts
-- Watch for column names with different data types
   or different lengths
SELECT  @@Servername AS Server ,
        DB_NAME() AS DBName ,
        Column_Name ,
        Data_Type ,
        Numeric_Precision AS Prec ,
        Numeric_Scale AS Scale ,
        Character_Maximum_Length ,
        COUNT(*) AS Count
FROM    information_schema.columns isc
        INNER JOIN information_schema.tables ist
               ON isc.table_name = ist.table_name
WHERE   Table_type = 'BASE TABLE'
GROUP BY Column_Name ,
        Data_Type ,
        Numeric_Precision ,
        Numeric_Scale ,
        Character_Maximum_Length;
 
-- Summary of data types
SELECT  @@Servername AS ServerName ,
        DB_NAME() AS DBName ,
        Data_Type ,
        Numeric_Precision AS Prec ,
        Numeric_Scale AS Scale ,
        Character_Maximum_Length AS [Length] ,
        COUNT(*) AS COUNT
FROM    information_schema.columns isc
        INNER JOIN information_schema.tables ist
               ON isc.table_name = ist.table_name
WHERE   Table_type = 'BASE TABLE'
GROUP BY Data_Type ,
        Numeric_Precision ,
        Numeric_Scale ,
        Character_Maximum_Length
ORDER BY Data_Type ,
        Numeric_Precision ,
        Numeric_Scale ,
        Character_Maximum_Length
 
-- Large object data types or Binary Large Objects(BLOBs)
SELECT  @@Servername AS ServerName ,
        DB_NAME() AS DBName ,
        isc.Table_Name ,
        Ordinal_Position AS Ord ,
        Column_Name ,
        Data_Type AS BLOB_Data_Type ,
        Numeric_Precision AS Prec ,
        Numeric_Scale AS Scale ,
        Character_Maximum_Length AS [Length]
FROM    information_schema.columns isc
        INNER JOIN information_schema.tables ist
               ON isc.table_name = ist.table_name
WHERE   Table_type = 'BASE TABLE'
        AND ( Data_Type IN ( 'text', 'ntext', 'image', 'XML' )
              OR ( Data_Type IN ( 'varchar', 'nvarchar', 'varbinary' )
                   AND Character_Maximum_Length = -1
                 )
            ) -- varchar(max), nvarchar(max), varbinary(max)
ORDER BY isc.Table_Name ,
        Ordinal_position;

3.1.1 Column Defaults

The default value is the value that will be saved if no value is specified for a column while inserting. Often, for a column that stores dates is getdate() or current_timestamp. Another common default in auditing is system_user, to identify the login that performed a certain action.

-- Table Defaults
SELECT  @@Servername AS ServerName ,
        DB_NAME() AS DBName ,
        parent.name AS TableName ,
        o.name AS Defaults ,
        o.[Type] ,
        o.Create_date
FROM    sys.objects o
        INNER JOIN sys.objects parent
               ON o.parent_object_id = parent.object_id
WHERE   o.[Type] = 'D' -- Defaults
ORDER BY parent.name ,
        o.NAME
 
--OR
-- Column Defaults
SELECT  @@Servername AS ServerName ,
        DB_NAME() AS DB_Name ,
        OBJECT_SCHEMA_NAME(parent_object_id) AS SchemaName ,
        OBJECT_NAME(parent_object_id) AS TableName ,
        parent_column_id AS Column_NBR ,
        Name AS DefaultName ,
        [type] ,
        type_desc ,
        create_date ,
        OBJECT_DEFINITION(object_id) AS Defaults
FROM    sys.default_constraints
ORDER BY TableName ,
        Column_NBR
--OR
-- Column Defaults
SELECT  @@Servername AS ServerName ,
        DB_NAME() AS DB_Name ,
        OBJECT_SCHEMA_NAME(t.object_id) AS SchemaName ,
        t.Name AS TableName ,
        c.Column_ID AS Ord ,
        c.Name AS Column_Name ,
        OBJECT_NAME(default_object_id) AS DefaultName ,
        OBJECT_DEFINITION(default_object_id) AS Defaults
FROM    sys.Tables t
        INNER JOIN sys.columns c ON t.object_id = c.object_id
WHERE   default_object_id <> 0
ORDER BY TableName ,
        SchemaName ,
        c.Column_ID
GO

3.1.2 Computed Columns

A computed column is a virtual column that is not physically stored in the table unless the column is marked PERSISTED. A computed column expression can use data from other columns to calculate a value for the column to which it belongs. You can specify an expression for a computed column in in SQL Server 2014 by using SQL Server Management Studio or Transact-SQL.

-- Computed columns
SELECT  @@Servername AS ServerName ,
        DB_NAME() AS DBName ,
        OBJECT_SCHEMA_NAME(object_id) AS SchemaName ,
        OBJECT_NAME(object_id) AS Tablename ,
        Column_id ,
        Name AS Computed_Column ,
        [Definition] ,
        is_persisted
FROM    sys.computed_columns
ORDER BY SchemaName ,
        Tablename ,
        [Definition];
--Or
-- Computed Columns
SELECT  @@Servername AS ServerName ,
        DB_NAME() AS DBName ,
        OBJECT_SCHEMA_NAME(t.object_id) AS SchemaName,
        t.Name AS TableName ,
        c.Column_ID AS Ord ,
        c.Name AS Computed_Column
FROM    sys.Tables t
        INNER JOIN sys.Columns c ON t.object_id = c.object_id
WHERE   is_computed = 1
ORDER BY t.Name ,
        SchemaName ,
        c.Column_ID
GO

Note that a computed column cannot be used as a DEFAULT or FOREIGN KEY constraint definition or with the NOT NULL constraint definition. However, if the computed column value is defined by a deterministic expression and the data type of the result is allowed in index columns, a computed column can be used as a key column in an index or as part of any PRIMARY KEY or UNIQUE constraint. For example, if the table has integer columns a and b, the computed column a + b may be indexed, but computed column a + DATEPART(dd, GETDATE()) cannot be indexed because the value might change in subsequent invocations. Also, a computed column cannot be the target of an INSERT or UPDATE statement.

3.1.3 Identity Columns

Identity columns can be used for generating key values. The identity property on a column guarantees the following:

  • Each new value is generated based on the current seed & increment.
  • Each new value for a particular transaction is different from other concurrent transactions on the table.
SELECT  @@Servername AS ServerName ,
        DB_NAME() AS DBName ,
        OBJECT_SCHEMA_NAME(object_id) AS SchemaName ,
        OBJECT_NAME(object_id) AS TableName ,
        Column_id ,
        Name AS IdentityColumn ,
        Seed_Value ,
        Last_Value
FROM    sys.identity_columns
ORDER BY SchemaName ,
        TableName ,
        Column_id;
GO

3.2 Keys and Indexes

To see which indexes exist on all tables in the current database.

SELECT  @@Servername AS ServerName ,
        DB_NAME() AS DB_Name ,
        o.Name AS TableName ,
        i.Name AS IndexName
FROM    sys.objects o
        INNER JOIN sys.indexes i ON o.object_id = i.object_id
WHERE   o.Type = 'U' -- User table
        AND LEFT(i.Name, 1) <> '_' -- Remove hypothetical indexes
ORDER BY o.NAME ,
        i.name;
GO

To see which indexes are missing?

-- Missing Indexes DMV Suggestions
SELECT  @@ServerName AS ServerName ,
        DB_NAME() AS DBName ,
        t.name AS 'Affected_table' ,
        ( LEN(ISNULL(ddmid.equality_columns, N'')
              + CASE WHEN ddmid.equality_columns IS NOT NULL
                       AND ddmid.inequality_columns IS NOT NULL THEN ','
                     ELSE ''
                END) - LEN(REPLACE(ISNULL(ddmid.equality_columns, N'')
                                   + CASE WHEN ddmid.equality_columns
                                                             IS NOT NULL
                                            AND ddmid.inequality_columns
                                                             IS NOT NULL
                                          THEN ','
                                          ELSE ''
                                     END, ',', '')) ) + 1 AS K ,
        COALESCE(ddmid.equality_columns, '')
        + CASE WHEN ddmid.equality_columns IS NOT NULL
                    AND ddmid.inequality_columns IS NOT NULL THEN ','
               ELSE ''
          END + COALESCE(ddmid.inequality_columns, '') AS Keys ,
        COALESCE(ddmid.included_columns, '') AS [include] ,
        'Create NonClustered Index IX_' + t.name + '_missing_'
        + CAST(ddmid.index_handle AS VARCHAR(20))
        + ' On ' + ddmid.[statement] COLLATE database_default
        + ' (' + ISNULL(ddmid.equality_columns, '')
        + CASE WHEN ddmid.equality_columns IS NOT NULL
                    AND ddmid.inequality_columns IS NOT NULL THEN ','
               ELSE ''
          END + ISNULL(ddmid.inequality_columns, '') + ')'
        + ISNULL(' Include (' + ddmid.included_columns + ');', ';')
                                                  AS sql_statement ,
        ddmigs.user_seeks ,
        ddmigs.user_scans ,
        CAST(( ddmigs.user_seeks + ddmigs.user_scans )
        * ddmigs.avg_user_impact AS BIGINT) AS 'est_impact' ,
        avg_user_impact ,
        ddmigs.last_user_seek ,
        ( SELECT    DATEDIFF(Second, create_date, GETDATE()) Seconds
          FROM      sys.databases
          WHERE     name = 'tempdb'
        ) SecondsUptime
-- Select *
FROM    sys.dm_db_missing_index_groups ddmig
        INNER JOIN sys.dm_db_missing_index_group_stats ddmigs
               ON ddmigs.group_handle = ddmig.index_group_handle
        INNER JOIN sys.dm_db_missing_index_details ddmid
               ON ddmig.index_handle = ddmid.index_handle
        INNER JOIN sys.tables t ON ddmid.OBJECT_ID = t.OBJECT_ID
WHERE   ddmid.database_id = DB_ID()
ORDER BY est_impact DESC;
GO

3.2.1 Foreign Keys

A foreign key (FK) is a column or combination of columns that is used to establish and enforce a link between the data in two tables to control the data that can be stored in the foreign key table. In a foreign key reference, a link is created between two tables when the column or columns that hold the primary key value for one table are referenced by the column or columns in another table. This column becomes a foreign key in the second table.

-- Foreign Keys
SELECT  @@Servername AS ServerName ,
        DB_NAME() AS DB_Name ,
        parent.name AS 'TableName' ,
        o.name AS 'ForeignKey' ,
        o.[Type] ,
        o.Create_date
FROM    sys.objects o
        INNER JOIN sys.objects parent ON o.parent_object_id =
                                                  parent.object_id
WHERE   o.[Type] = 'F' -- Foreign Keys
ORDER BY parent.name ,
        o.name
--OR
SELECT  f.name AS ForeignKey ,
        SCHEMA_NAME(f.SCHEMA_ID) AS SchemaName ,
        OBJECT_NAME(f.parent_object_id) AS TableName ,
        COL_NAME(fc.parent_object_id, fc.parent_column_id) AS
                                          ColumnName ,
        SCHEMA_NAME(o.SCHEMA_ID) ReferenceSchemaName ,
        OBJECT_NAME(f.referenced_object_id) AS ReferenceTableName ,
        COL_NAME(fc.referenced_object_id, fc.referenced_column_id)
                                              AS ReferenceColumnName
FROM    sys.foreign_keys AS f
        INNER JOIN sys.foreign_key_columns AS fc
               ON f.OBJECT_ID = fc.constraint_object_id
        INNER JOIN sys.objects AS o ON o.OBJECT_ID =
                                              fc.referenced_object_id
ORDER BY TableName ,
        ReferenceTableName;
GO

The result will be as follows:
Foreign_Keys_Output

3.2.3 Missing Indexes that support Foreign Keys

It is recommended to have an index associated with each foreign key. This ensures faster table joins, which are typically joined on foreign key columns anyway. Indexes on foreign keys also provide faster deletes. If these supporting indexes are missing, SQL will perform a table scale on the related table each time a record in the first table is deleted.

-- Foreign Keys missing indexes
-- Note this script only works for creating single column indexes.
-- Multiple FK columns are out of scope for this script.
SELECT  DB_NAME() AS DBName ,
        rc.Constraint_Name AS FK_Constraint ,
-- rc.Constraint_Catalog AS FK_Database,
-- rc.Constraint_Schema AS FKSch,
        ccu.Table_Name AS FK_Table ,
        ccu.Column_Name AS FK_Column ,
        ccu2.Table_Name AS ParentTable ,
        ccu2.Column_Name AS ParentColumn ,
        I.Name AS IndexName ,
        CASE WHEN I.Name IS NULL
             THEN 'IF NOT EXISTS (SELECT * FROM sys.indexes
                           WHERE object_id = OBJECT_ID(N'''
             + RC.Constraint_Schema + '.' + ccu.Table_Name
             + ''') AND name = N''IX_' + ccu.Table_Name + '_'
             + ccu.Column_Name + ''') '
             + 'CREATE NONCLUSTERED INDEX IX_' + ccu.Table_Name + '_'
             + ccu.Column_Name + ' ON ' + rc.Constraint_Schema + '.'
             + ccu.Table_Name + '( ' + ccu.Column_Name
             + ' ASC ) WITH (PAD_INDEX = OFF,
                           STATISTICS_NORECOMPUTE = OFF,
                           SORT_IN_TEMPDB = ON, IGNORE_DUP_KEY = OFF,
                           DROP_EXISTING = OFF, ONLINE = ON);'
             ELSE ''
        END AS SQL
FROM    information_schema.referential_constraints RC
        JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu
         ON rc.CONSTRAINT_NAME = ccu.CONSTRAINT_NAME
        JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu2
         ON rc.UNIQUE_CONSTRAINT_NAME = ccu2.CONSTRAINT_NAME
        LEFT JOIN sys.columns c ON ccu.Column_Name = C.name
                       AND ccu.Table_Name = OBJECT_NAME(C.OBJECT_ID)
        LEFT JOIN sys.index_columns ic ON C.OBJECT_ID = IC.OBJECT_ID
                                      AND c.column_id = ic.column_id
                                      AND index_column_id = 1
                                  -- index found has the foreign key
                                  --  as the first column
        LEFT JOIN sys.indexes i ON IC.OBJECT_ID = i.OBJECT_ID
                                   AND ic.index_Id = i.index_Id
WHERE   I.name IS NULL
ORDER BY FK_table ,
        ParentTable ,
        ParentColumn;
GO

3.3 Object Dependencies

Some database objects have dependencies upon other database objects. For example, views and stored procedures depend on the existence of tables that contain the data returned by the view or procedure. An object that references another object in its definition and that definition is stored in the system catalog is called a referencing entity. An object that is referred to by another object is called a referenced entity.
sp_msdependencies is an SQL Server undocumented stored procedure that can be helpful in navigating complex table interdependencies.

EXEC sp_msdependencies '?' -- Displays Help
 
sp_MSobject_dependencies name = NULL, type = NULL, flags = 0x01fd
  name:  name or null (all objects of type)
  type:  type number (see below) or null
         if both null, get all objects in database
  flags is a bitmask of the following values:
         0x10000  = return multiple parent/child rows per object
         0x20000  = descending return order
         0x40000  = return children instead of parents
         0x80000  = Include input object in output result set
         0x100000 = return only firstlevel (immediate) parents/children
         0x200000 = return only DRI dependencies
         power(2, object type number(s))  to return in results set:
                0 (1        - 0x0001)     - UDF
                1 (2        - 0x0002)     - system tables
                2 (4        - 0x0004)     - view
                3 (8        - 0x0008)     - user table
                4 (16              - 0x0010)     - procedure
                5 (32              - 0x0020)     - log
                6 (64       - 0x0040)     - default
                7 (128      - 0x0080)     - rule
                8 (256      - 0x0100)     - trigger
                12 (1024    - 0x0400) - uddt
         shortcuts:
                29   (0x011c) - trig, view, user table, procedure
                448  (0x00c1) - rule, default, datatype
                4606 (0x11fd) - all but systables/objects
                4607 (0x11ff) - all

If we list all dependencies using sp_msdependencies, it will return four columns: Type, ObjName, Owner (Schema) and Sequence.

3.3.1 Query the System Catalog Views

To query the foreign key relationships system tables:

--Independent tables
SELECT  Name AS InDependentTables
FROM    sys.tables
WHERE   object_id NOT IN ( SELECT referenced_object_id
                             FROM   sys.foreign_key_columns )
                                         -- Check for parents
        AND object_id NOT IN ( SELECT parent_object_id
                             FROM   sys.foreign_key_columns )
                                       -- Check for Dependents
ORDER BY Name
 
-- Tables with dependencies.
SELECT DISTINCT
        OBJECT_NAME(referenced_object_id) AS ParentTable ,
        OBJECT_NAME(parent_object_id) AS DependentTable ,
        OBJECT_NAME(constraint_object_id) AS ForeignKeyName
FROM    sys.foreign_key_columns
ORDER BY ParentTable ,
        DependentTable
 
-- Top level of the pyramid tables. Tables with no parents.
SELECT DISTINCT
        OBJECT_NAME(referenced_object_id) AS TablesWithNoParent
FROM    sys.foreign_key_columns
WHERE   referenced_object_id NOT IN ( SELECT  parent_object_id
                               FROM    sys.foreign_key_columns )
ORDER BY 1
 
-- Bottom level of the pyramid tables.
-- Tables with no dependents. (These are the leaves on a tree.)
SELECT DISTINCT
        OBJECT_NAME(parent_object_id) AS TablesWithNoDependents
FROM    sys.foreign_key_columns
WHERE   parent_object_id NOT IN ( SELECT  referenced_object_id
                               FROM    sys.foreign_key_columns )
ORDER BY 1
 
-- Tables with both parents and dependents.
-- Tables in the middle of the hierarchy
SELECT DISTINCT
        OBJECT_NAME(referenced_object_id) AS MiddleTables
FROM    sys.foreign_key_columns
WHERE   referenced_object_id IN ( SELECT  parent_object_id
                               FROM    sys.foreign_key_columns )
      AND parent_object_id NOT IN ( SELECT  referenced_object_id
                               FROM    sys.foreign_key_columns )
ORDER BY 1;
 
-- you might find a self-referencing dependent table.
-- Recursive (self) referencing table dependencies.
SELECT DISTINCT
        OBJECT_NAME(referenced_object_id) AS ParentTable ,
        OBJECT_NAME(parent_object_id) AS ChildTable ,
        OBJECT_NAME(constraint_object_id) AS ForeignKeyName
FROM    sys.foreign_key_columns
WHERE   referenced_object_id = parent_object_id
ORDER BY 1 ,
        2;

3.3.2 Using a Common Table Expression (CTE)

To solve a recursive query using a Common Table Expression (CTE).

-- How to find the hierarchical dependencies
-- Solve recursive queries using Common Table Expressions (CTE)
WITH    TableHierarchy ( ParentTable, DependentTable, Level )
          AS (
-- Anchor member definition (First level group to start the process)
               SELECT DISTINCT
                        CAST(NULL AS INT) AS ParentTable ,
                        e.referenced_object_id AS DependentTable ,
                        0 AS Level
               FROM     sys.foreign_key_columns AS e
               WHERE    e.referenced_object_id NOT IN (
                        SELECT  parent_object_id
                        FROM    sys.foreign_key_columns )
-- Add filter dependents of only one parent table
-- AND Object_Name(e.referenced_object_id) = 'User'
               UNION ALL
-- Recursive member definition (Find all the layers of dependents)
               SELECT --Distinct
                        e.referenced_object_id AS ParentTable ,
                        e.parent_object_id AS DependentTable ,
                        Level + 1
               FROM     sys.foreign_key_columns AS e
                        INNER JOIN TableHierarchy AS d
                               ON ( e.referenced_object_id ) =
                                                      d.DependentTable
             )
    -- Statement that executes the CTE
SELECT DISTINCT
        OBJECT_NAME(ParentTable) AS ParentTable ,
        OBJECT_NAME(DependentTable) AS DependentTable ,
        Level
FROM    TableHierarchy
ORDER BY Level ,
        ParentTable ,
        DependentTable;

Summary

You can spend a couple of hours to get acquainted with any database design by using best practices provided in this article.
This article provides sample scripts that you can copy and run on the SQL Server and a database you are currently using.

Andrey Langovoy