{"id":1881,"date":"2014-10-09T00:00:00","date_gmt":"2014-10-09T00:00:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/exploring-your-sql-server-databases-with-t-sql\/"},"modified":"2021-08-24T13:39:41","modified_gmt":"2021-08-24T13:39:41","slug":"exploring-your-sql-server-databases-with-t-sql","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/database-administration-sql-server\/exploring-your-sql-server-databases-with-t-sql\/","title":{"rendered":"Exploring Your SQL Server Databases with T-SQL"},"content":{"rendered":"
\n
\n
    \n
  1. Exploring your Servers<\/a>\n
      \n
    1. Basic Server Information<\/a><\/li>\n
    2. Linked Servers<\/a><\/li>\n
    3. List All Databases<\/a><\/li>\n
    4. Last Databases Backup?<\/a><\/li>\n
    5. Active User Connections by Database<\/a><\/li>\n<\/ol>\n<\/li>\n
    6. Basic Database Exploration<\/a>\n
        \n
      1. Database File Location<\/a><\/li>\n
      2. Tables<\/a>\n
          \n
        1. Row Counts for all Tables<\/a><\/li>\n
        2. sp_msforeachtable<\/a><\/li>\n
        3. A faster way- use the clustered index<\/a><\/li>\n
        4. Finding Heaps <\/a><\/li>\n
        5. Investigating Table Activity<\/a><\/li>\n<\/ol>\n<\/li>\n
        6. Views<\/a><\/li>\n
        7. Synonyms<\/a><\/li>\n
        8. Stored Procedures<\/a><\/li>\n
        9. Functions<\/a><\/li>\n
        10. Triggers<\/a><\/li>\n
        11. CHECK Constraints<\/a><\/li>\n<\/ol>\n<\/li>\n
        12. Exploring your Data Model in depth<\/a>\n
            \n
          1. Columns<\/a>\n
              \n
            1. Column Defaults<\/a><\/li>\n
            2. Computed columns<\/a><\/li>\n
            3. Identity Columns<\/a><\/li>\n<\/ol>\n<\/li>\n
            4. Keys and Indexes<\/a>\n
                \n
              1. What indexes exist? \t\t\t<\/a><\/li>\n
              2. Which indexes are missing? \t\t\t<\/a><\/li>\n
              3. Foreign Keys<\/a><\/li>\n
              4. \t\t\t<\/a>Missing Indexes that support Foreign Keys. \t\t<\/a><\/li>\n<\/ol>\n

                \t\t <\/a><\/li>\n<\/ol>\n

                \t\t<\/a><\/li>\n

              5. <\/a>Object Dependencies<\/a>\n
                  \n
                1. sp_msdependencies<\/a><\/li>\n
                2. Query the system catalog views<\/a><\/li>\n
                3. Using a Common Table Expression (CTE)<\/a><\/li>\n<\/ol>\n<\/li>\n<\/ol>\n<\/div>\n

                  As a DBA and performance tuning consultant, for Ambient Consulting in Minneapolis, I’m often asked to analyze performance issues on a SQL Server instance with which I’m unfamiliar. It can be a daunting task. Let’s face it, most of the time companies do not have good documentation about their databases. Or if they do, it is outdated, or it takes a few days to locate and retrieve it.<\/p>\n

                  In this article, I share a set of basic scripts that I’ve developed, over the years, which mine the metadata in the various system functions, stored procedures, tables, catalog views, and dynamic management views, Together, they reveal all the secrets of the databases on the instance, their size, file locations, and their design, including columns, data types, defaults, keys and indexes.<\/p>\n

                  If you’ve been used to retrieving some of this information by clinging to the mouse with whited knuckles and stabbing at the screen, then I hope you’ll be pleasantly surprised at the wealth of information some of these simple scripts reveal, instantly. <\/p>\n

                  As with any scripts, always test them before running them in a production environment. I recommend that you start first with one of the SQL Server sample databases like AdventureWorks<\/code> or pubs<\/code> that you can download from: AdventureWorksDW Databases – 2012, 2008R2 and 2008<\/a><\/span>.<\/p>\n

                  OK, enough preamble, just show me the scripts!<\/p>\n

                  Exploring your Servers<\/h2>\n

                  We’ll start with some queries that offer the server-level view of your databases.<\/p>\n

                  Basic Server Information<\/h3>\n

                  First, some simple @@functions<\/code> that provide basic server information.<\/p>\n

                  \t-- Server and instance name \n\tSelect @@SERVERNAME as [Server\\Instance]; \n\t-- SQL Server Version \n\tSelect @@VERSION as SQLServerVersion; \n\t-- SQL Server Instance \n\tSelect @@ServiceName AS ServiceInstance;\n\t \n\t-- Current Database \n\tSelect DB_NAME() AS CurrentDB_Name; \n<\/pre>\n
                  Listing 1: Basic server information<\/p>\n
                  How long has your server been running since the last SQL Server startup? Note  the  tempdb<\/code> system database is recreated every time the server  restarts. Thus this is one method to tell when the database server was last restarted.<\/p>\n
                  \t-- Note the tempdb system database is recreated every time the server restarts\n\t-- Thus this is one method to tell when the database server was last restarted\n\tSELECT  @@Servername AS ServerName ,\n\t        create_date AS ServerStarted ,\n\t        DATEDIFF(s, create_date, GETDATE()) \/ 86400.0 AS DaysRunning ,\n\t        DATEDIFF(s, create_date, GETDATE()) AS SecondsRunnig\n\tFROM    sys.databases\n\tWHERE   name = 'tempdb'; \n\tGO\n<\/pre>\n
                  Listing 2: how long has your server been running since startup?<\/p>\n
                  Linked Servers<\/h3>\n
                  Linked Servers are database connections set up to allow communication from SQL  Server to other data servers. Distributed queries can be ran against these linked servers. It’s good to know if your  database server is an isolated self-contained database server or if there are links to other database servers.<\/p>\n
                  \tEXEC sp_helpserver; \n\t--OR \n\tEXEC sp_linkedservers; \n\t--OR \n\tSELECT  @@SERVERNAME AS Server ,\n\t        Server_Id AS LinkedServerID ,\n\t        name AS LinkedServer ,\n\t        Product ,\n\t        Provider ,\n\t        Data_Source ,\n\t        Modify_Date\n\tFROM    sys.servers\n\tORDER BY name; \n\tGO\n<\/pre>\n
                  Listing 3: Linked Servers<\/p>\n
                  List All Databases<\/h3>\n
                  First step is to take inventory of all the databases found on the server. Note  the four or five system databases (master<\/code>,   model<\/code>,  msdb<\/code>,  tempdb<\/code>,  and   distribution<\/code> if you are using replication). You may want to exclude these system  databases in future queries. It is very easy to see a list of database directly from SQL Server Management Studio SQL  Server (SSMS). However, these simple database queries are building blocks for more complicated queries.  <\/p>\n
                  There are several ways to get a list of the databases in T-SQL, and Listing 4  presents just a few of them. Each method produces a similar result set, but with subtle differences.<\/p>\n
                  \tEXEC sp_helpdb; \n\t--OR \n\tEXEC sp_Databases; \n\t--OR \n\tSELECT  @@SERVERNAME AS Server ,\n\t        name AS DBName ,\n\t        recovery_model_Desc AS RecoveryModel ,\n\t        Compatibility_level AS CompatiblityLevel ,\n\t        create_date ,\n\t        state_desc\n\tFROM    sys.databases\n\tORDER BY Name; \n\t--OR \n\tSELECT  @@SERVERNAME AS Server ,\n\t        d.name AS DBName ,\n\t        create_date ,\n\t        compatibility_level ,\n\t        m.physical_name AS FileName\n\tFROM    sys.databases d\n\t        JOIN sys.master_files m ON d.database_id = m.database_id\n\tWHERE   m.[type] = 0 -- data files only\n\tORDER BY d.name; \n\tGO\n<\/pre>\n
                  Listing 4: Database Inventory<\/p>\n
                  Last Databases Backup?<\/h3>\n
                  Stop! Before you go any further, every good DBA should be certain they have  recent database backup(s). <\/p>\n
                  \tSELECT  @@Servername AS ServerName ,\n\t        d.Name AS DBName ,\n\t        MAX(b.backup_finish_date) AS LastBackupCompleted\n\tFROM    sys.databases d\n\t        LEFT OUTER JOIN msdb..backupset b\n\t                    ON b.database_name = d.name\n\t                       AND b.[type] = 'D'\n\tGROUP BY d.Name\n\tORDER BY d.Name; \n<\/pre>\n
                  Listing 5: Last Database Backup<\/p>\n
                  Better still if you know the physical file location of the last backups.<\/p>\n
                  \tSELECT  @@Servername AS ServerName ,\n\t        d.Name AS DBName ,\n\t        b.Backup_finish_date ,\n\t        bmf.Physical_Device_name\n\tFROM    sys.databases d\n\t        INNER JOIN msdb..backupset b ON b.database_name = d.name\n\t                                        AND b.[type] = 'D'\n\t        INNER JOIN msdb.dbo.backupmediafamily bmf ON b.media_set_id = bmf.media_set_id\n\tORDER BY d.NAME ,\n\t        b.Backup_finish_date DESC; \n\tGO\n<\/pre>\n
                  Listing 6: Physical file location for recent backups<\/p>\n
                  Active User Connections by Database<\/h3>\n
                  It’s a good idea to have an understanding of what databases are being used at  any point in time, especially if you are experiencing performance problems. <\/p>\n
                  \t-- Similar information can be derived from sp_who \n\tSELECT  @@Servername AS Server ,\n\t        DB_NAME(database_id) AS DatabaseName ,\n\t        COUNT(database_id) AS Connections ,\n\t        Login_name AS LoginName ,\n\t        MIN(Login_Time) AS Login_Time ,\n\t        MIN(COALESCE(last_request_end_time, last_request_start_time))\n                                                         AS Last_Batch\n\tFROM    sys.dm_exec_sessions\n\tWHERE   database_id > 0\n\t        AND DB_NAME(database_id) NOT IN ( 'master', 'msdb' )\n\tGROUP BY database_id ,\n\t         login_name\n\tORDER BY DatabaseName;\n<\/pre>\n
                  Listing 7: Active connections by database<\/p>\n
                  Basic Database Exploration<\/h2>\n
                  Let’s now drill down and see how we can gather information about the objects in  each of our databases, using various catalog views and Dynamic management Views. Most of the queries presented in this  section let’s look at the individual database so remember to change the current database in SSMS or by a   Use database;<\/code> command. Remember to you can always check  the current default database with  Select DB_NAME();<\/code>.<\/p>\n
                  The  sys.objects<\/code> system table is one of the key system tables  for gathering a lot of information on the objects that comprise your data model, with  sys.objects.type<\/code> being the key column on which to filter.<\/p>\n
                  \t-- In this example U is for tables. \n\t-- Try swapping in one of the many other types. \n\tUSE MyDatabase;\n\tGO\n\tSELECT  *\n\tFROM    sys.objects\n\tWHERE   type = 'U'; \n<\/pre>\n
                  Listing 8: Listing out all user-defined tables in a database<\/p>\n
                  The following table shows the list of objects types on which we can filter (see  also the  sys.objects<\/a> documentation on Microsoft’s MSDN  website).<\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n
                  \n
                  sys.objects.type<\/p>\n<\/td>\n<\/tr>\n
                  \n
                  AF = Aggregate function (CLR)<\/p>\n<\/td>\n
                  		\n
                  P = SQL Stored Procedure<\/p>\n<\/td>\n
                  		\n
                  TA = Assembly (CLR) DML trigger<\/p>\n<\/td>\n<\/tr>\n
                  \n
                  C = CHECK constraint<\/p>\n<\/td>\n
                  		\n
                  PC = Assembly (CLR) stored-procedure<\/p>\n<\/td>\n
                  		\n
                  TF = SQL table-valued-function<\/p>\n<\/td>\n<\/tr>\n
                  \n
                  D = DEFAULT (constraint or stand-alone)<\/p>\n<\/td>\n
                  		\n
                  PG = Plan guide<\/p>\n<\/td>\n
                  		\n
                  TR = SQL DML trigger<\/p>\n<\/td>\n<\/tr>\n
                  \n
                  F = FOREIGN KEY constraint<\/p>\n<\/td>\n
                  		\n
                  PK = PRIMARY KEY constraint<\/p>\n<\/td>\n
                  		\n
                  TT = Table type<\/p>\n<\/td>\n<\/tr>\n
                  \n
                  FN = SQL scalar function<\/p>\n<\/td>\n
                  		\n
                  R = Rule (old-style, stand-alone)<\/p>\n<\/td>\n
                  		\n
                  U = Table (user-defined)<\/p>\n<\/td>\n<\/tr>\n
                  \n
                  FS = Assembly (CLR) scalar-function<\/p>\n<\/td>\n
                  		\n
                  RF = Replication-filter-procedure<\/p>\n<\/td>\n
                  		\n
                  UQ = UNIQUE constraint<\/p>\n<\/td>\n<\/tr>\n
                  \n
                  FT = Assembly (CLR) table-valued function <\/p>\n<\/td>\n
                  		\n
                  S = System base table<\/p>\n<\/td>\n
                  		\n
                  V = View<\/p>\n<\/td>\n<\/tr>\n
                  \n
                  IF = SQL inline table-valued function<\/p>\n<\/td>\n
                  		\n
                  SN = Synonym<\/p>\n<\/td>\n
                  		\n
                  X = Extended stored procedure<\/p>\n<\/td>\n<\/tr>\n
                  \n
                  IT = Internal table<\/p>\n<\/td>\n
                  		\n
                  SQ = Service queue<\/p>\n<\/td>\n
                  		 \t\t \t\t<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
                  Other catalog views, such as  sys.tables<\/code> and    sys.views<\/code>, inherit from   sys.objects<\/code> and provide the information for that particular object type. With these  views, plus the   OBJECTPROPERTY<\/code> metadata function, we can uncover a great deal of information on each of  the objects that make up our database schemas.<\/p>\n
                  Database File Location<\/h3>\n
                  Physical location and drive of the current database file, including the master  database file (*.mdf<\/code>) and the Log database  file (*.ldf<\/code>) can be found using these queries.<\/p>\n
                  \tEXEC sp_Helpfile; \n\t--OR \n\tSELECT  @@Servername AS Server ,\n\t        DB_NAME() AS DB_Name ,\n\t        File_id ,\n\t        Type_desc ,\n\t        Name ,\n\t        LEFT(Physical_Name, 1) AS Drive ,\n\t        Physical_Name ,\n\t        RIGHT(physical_name, 3) AS Ext ,\n\t        Size ,\n\t        Growth\n\tFROM    sys.database_files\n\tORDER BY File_id; \n\tGO\n<\/pre>\n
                  Listing 9: Physical file location of the current database<\/p>\n
                  Tables<\/h3>\n
                  Of course, the Object Explorer in SSMS provides a convenient list of the tables  in a specific database, but using scripts we can unveil information that isn’t easily available via the GUI. The ANSI  Standard approach is to use the  INFORMATION_SCHEMA<\/code> views, but they won’t return  information regarding objects that are not part of the Standard (such as triggers, extended properties, and so on), so  use of the SQL Server Catalog views is common.<\/p>\n
                  \tEXEC sp_tables; -- Note this method returns both table and views. \n\t--OR \n\tSELECT  @@Servername AS ServerName ,\n\t        TABLE_CATALOG ,\n\t        TABLE_SCHEMA ,\n\t        TABLE_NAME\n\tFROM    INFORMATION_SCHEMA.TABLES\n\tWHERE   TABLE_TYPE = 'BASE TABLE'\n\tORDER BY TABLE_NAME ;\n\t--OR\n\tSELECT  @@Servername AS ServerName ,\n\t        DB_NAME() AS DBName ,\n\t        o.name AS 'TableName' ,\n\t        o.[Type] ,\n\t        o.create_date\n\tFROM    sys.objects o\n\tWHERE   o.Type = 'U' -- User table \n\tORDER BY o.name;\n\t--OR \n\tSELECT  @@Servername AS ServerName ,\n\t        DB_NAME() AS DBName ,\n\t        t.Name AS TableName,\n\t        t.[Type],\n\t        t.create_date\n\tFROM    sys.tables t\n\tORDER BY t.Name;\n\tGO\n<\/pre>\n
                  Listing 10: Exploring table details<\/p>\n
                  Row Counts for all Tables<\/h4>\n
                  If you know nothing about a table, all tables are equal. The more you know about  the tables the more you can mentally begin to determine which tables are more important and which tables are less  important. Generally speaking, tables with the largest number of rows tend to be the ones that suffer most often from  performance issues.<\/p>\n
                  From SSMS object explorer, we can right-click on any table name and select Properties<\/span>, and view the Storage<\/span> page will provide a row count  for that table. <\/p>\n
                   \"2066-img135.jpg\"<\/p>\n
                  However, it’s hard to collect this information for all tables, manually.  Likewise, the brute force approach of executing  SELECT COUNT(*) FROM TABLENAME;<\/code>  for every table is likely to involve a lot of typing.<\/p>\n
                  A much better approach is to use T-SQL as a script generator. The script in  Listing 11 will generate a set of T-SQL statements to return the row count for each table in the current database.  Simply execute it, then copy-and-paste the generated statements into the query window and execute them.<\/p>\n
                  \tSELECT  'Select ''' + DB_NAME() + '.' + SCHEMA_NAME(SCHEMA_ID) + '.'\n\t        + LEFT(o.name, 128) + ''' as DBName, count(*) as Count From ' + o.name\n\t        + ';' AS ' Script generator to get counts for all tables'\n\tFROM    sys.objects o\n\tWHERE   o.[type] = 'U'\n\tORDER BY o.name;\n\tGO\n<\/pre>\n
                  Listing 11: A script to generate a script to return row counts for all tables<\/p>\n
                   \"2066-img138.jpg\"<\/p>\n
                  sp_msforeachtable<\/h4>\n
                   sp_msforeachtable<\/code>  is an undocumented Microsoft function that will loop through all the tables in a database executing a query, and  replacing ‘?’ with each table name. There is also a similar database level function called   sp_msforeachdb<\/code>.  <\/p>\n
                  There are some known issues with these undocumented functions, as they do not  handle special characters in the object names. For example, table or database names containing a dash character (“-“)  will cause the stored procedure to fail.<\/p>\n
                  \tCREATE TABLE #rowcount\n\t     ( Tablename VARCHAR(128) ,\n\t      Rowcnt INT ); \n\tEXEC sp_MSforeachtable 'insert into #rowcount select ''?'', count(*) from ?' \n\tSELECT  *\n\tFROM    #rowcount\n\tORDER BY Tablename ,\n\t        Rowcnt; \n\tDROP TABLE #rowcount;\n<\/pre>\n
                  Listing 12: Using sp_msforeachtable to return row counts for all tables<\/p>\n
                  A faster way to get row counts – use the clustered index<\/h4>\n
                  All of the previous methods to return row counts for each table rely on use of  COUNT(*)<\/code>, which performs poorly for tables with more than  about 500K rows, in my experience.<\/p>\n
                  A faster way to get table row counts is to get the record counts from the  clustered index or heap partition. Note while this method is much faster, Microsoft has indicated the record count  updates on indexes may not always match the record counts of the table, due to a delay in the index counts getting  updated. In most cases they are exactly the same or very, very close and will be the same shortly.<\/p>\n
                  \t-- A faster way to get table row counts. \n\t-- Hint: get it from an index, not the table.\n\tSELECT  @@ServerName AS Server ,\n\t        DB_NAME() AS DBName ,\n\t        OBJECT_SCHEMA_NAME(p.object_id) AS SchemaName ,\n\t        OBJECT_NAME(p.object_id) AS TableName ,\n\t        i.Type_Desc ,\n\t        i.Name AS IndexUsedForCounts ,\n\t        SUM(p.Rows) AS Rows\n\tFROM    sys.partitions p\n\t        JOIN sys.indexes i ON i.object_id = p.object_id\n\t                              AND i.index_id = p.index_id\n\tWHERE   i.type_desc IN ( 'CLUSTERED', 'HEAP' )\n                             -- This is key (1 index per table) \n\t        AND OBJECT_SCHEMA_NAME(p.object_id) <> 'sys'\n\tGROUP BY p.object_id ,\n\t        i.type_desc ,\n\t        i.Name\n\tORDER BY SchemaName ,\n\t        TableName; \n\t \n\t-- OR \n\t-- Similar method to get row counts, but this uses DMV dm_db_partition_stats \n\tSELECT  @@ServerName AS ServerName ,\n\t        DB_NAME() AS DBName ,\n\t        OBJECT_SCHEMA_NAME(ddps.object_id) AS SchemaName ,\n\t        OBJECT_NAME(ddps.object_id) AS TableName ,\n\t        i.Type_Desc ,\n\t        i.Name AS IndexUsedForCounts ,\n\t        SUM(ddps.row_count) AS Rows\n\tFROM    sys.dm_db_partition_stats ddps\n\t        JOIN sys.indexes i ON i.object_id = ddps.object_id\n\t                              AND i.index_id = ddps.index_id\n\tWHERE   i.type_desc IN ( 'CLUSTERED', 'HEAP' )\n                              -- This is key (1 index per table) \n\t        AND OBJECT_SCHEMA_NAME(ddps.object_id) <> 'sys'\n\tGROUP BY ddps.object_id ,\n\t        i.type_desc ,\n\t        i.Name\n\tORDER BY SchemaName ,\n\t        TableName;\n\tGO\n<\/pre>\n
                  Listing 13: Return table row counts from the index or table partition<\/p>\n
                  Finding Heaps (tables with no clustered index)<\/h4>\n
                  Working with heap tables is like working with a flat file, instead of a  database. If you want to guarantee a full table scan for any and all queries, use a heap table. My general  recommendation would be to add a primary key clustered index to all heap tables.<\/p>\n
                  \t-- Heap tables (Method 1) \n\tSELECT  @@Servername AS ServerName ,\n\t        DB_NAME() AS DBName ,\n\t        t.Name AS HeapTable ,\n\t        t.Create_Date\n\tFROM    sys.tables t\n\t        INNER JOIN sys.indexes i ON t.object_id = i.object_id\n\t                                    AND i.type_desc = 'HEAP'\n\tORDER BY t.Name \n\t--OR \n\t-- Heap tables (Method 2) \n\tSELECT  @@Servername AS ServerName ,\n\t        DB_NAME() AS DBName ,\n\t        t.Name AS HeapTable ,\n\t        t.Create_Date\n\tFROM    sys.tables t\n\tWHERE   OBJECTPROPERTY(OBJECT_ID, 'TableHasClustIndex') = 0\n\tORDER BY t.Name; \n\t--OR \n\t-- Heap tables (Method 3) also provides row counts \n\tSELECT  @@ServerName AS Server ,\n\t        DB_NAME() AS DBName ,\n\t        OBJECT_SCHEMA_NAME(ddps.object_id) AS SchemaName ,\n\t        OBJECT_NAME(ddps.object_id) AS TableName ,\n\t        i.Type_Desc ,\n\t        SUM(ddps.row_count) AS Rows\n\tFROM    sys.dm_db_partition_stats AS ddps\n\t        JOIN sys.indexes i ON i.object_id = ddps.object_id\n\t                              AND i.index_id = ddps.index_id\n\tWHERE   i.type_desc = 'HEAP'\n\t        AND OBJECT_SCHEMA_NAME(ddps.object_id) <> 'sys'\n\tGROUP BY ddps.object_id ,\n\t        i.type_desc\n\tORDER BY TableName; \n<\/pre>\n
                  Listing 14: Finding heaps<\/p>\n
                  Investigating Table Activity<\/h4>\n
                  Knowing which tables have the most reads and writes is another important piece  of information when performance tuning your database. Previously, we examined queries to return the row counts for each  table. The following examples show the number of table reads and writes.<\/p>\n
                  Note these statistics from Dynamic Management Views are cleared out each time  SQL Server restarts (wait and latch statistics can also be cleared out manually<\/a>). The longer the server has  been up, the more reliable the statistics. I have a lot more confidence with statistics that are over 30 days (assumes  the tables have been through a month end cycle) and a lot less confidence if they are less than 7 days.  <\/p>\n
                  \t-- Table Reads and Writes \n\t-- Heap tables out of scope for this query. Heaps do not have indexes. \n\t-- Only lists tables referenced since the last server restart \n\tSELECT  @@ServerName AS ServerName ,\n\t        DB_NAME() AS DBName ,\n\t        OBJECT_NAME(ddius.object_id) AS TableName ,\n\t        SUM(ddius.user_seeks + ddius.user_scans + ddius.user_lookups)\n                                                               AS Reads ,\n\t        SUM(ddius.user_updates) AS Writes ,\n\t        SUM(ddius.user_seeks + ddius.user_scans + ddius.user_lookups\n\t            + ddius.user_updates) AS [Reads&Writes] ,\n\t        ( SELECT    DATEDIFF(s, create_date, GETDATE()) \/ 86400.0\n\t          FROM      master.sys.databases\n\t          WHERE     name = 'tempdb'\n\t        ) AS SampleDays ,\n\t        ( SELECT    DATEDIFF(s, create_date, GETDATE()) AS SecoundsRunnig\n\t          FROM      master.sys.databases\n\t          WHERE     name = 'tempdb'\n\t        ) AS SampleSeconds\n\tFROM    sys.dm_db_index_usage_stats ddius\n\t        INNER JOIN sys.indexes i ON ddius.object_id = i.object_id\n\t                                     AND i.index_id = ddius.index_id\n\tWHERE   OBJECTPROPERTY(ddius.object_id, 'IsUserTable') = 1\n\t        AND ddius.database_id = DB_ID()\n\tGROUP BY OBJECT_NAME(ddius.object_id)\n\tORDER BY [Reads&Writes] DESC;\n\tGO\n<\/pre>\n
                  Listing 15: Read and write activity for all tables referenced since the last  server restart, in a database<\/p>\n
                  A more advanced version of the same query uses a cursor to consolidate the  information for all Tables for all databases on the server. While I am not a fan of cursors due to their slow  performance, navigating multiple databases does seem to be a good use for one.<\/p>\n
                  \t-- Table Reads and Writes \n\t-- Heap tables out of scope for this query. Heaps do not have indexes. \n\t-- Only lists tables referenced since the last server restart \n\t-- This query uses a cursor to identify all the user databases on the server \n\t-- Consolidates individual database results into a report, using a temp table. \n\tDECLARE DBNameCursor CURSOR\n\tFOR\n\t    SELECT  Name\n\t    FROM    sys.databases\n\t    WHERE   Name NOT IN ( 'master', 'model', 'msdb', 'tempdb',\n\t                            'distribution' )\n\t    ORDER BY Name; \n\tDECLARE @DBName NVARCHAR(128) \n\tDECLARE @cmd VARCHAR(4000) \n\tIF OBJECT_ID(N'tempdb..TempResults') IS NOT NULL\n\t    BEGIN \n\t        DROP TABLE tempdb..TempResults \n\t    END \n\tCREATE TABLE tempdb..TempResults\n\t     (\n\t      ServerName NVARCHAR(128) ,\n\t      DBName NVARCHAR(128) ,\n\t      TableName NVARCHAR(128) ,\n\t      Reads INT ,\n\t      Writes INT ,\n\t      ReadsWrites INT ,\n\t      SampleDays DECIMAL(18, 8) ,\n\t      SampleSeconds INT\n\t    ) \n\tOPEN DBNameCursor \n\tFETCH NEXT FROM DBNameCursor INTO @DBName \n\tWHILE @@fetch_status = 0\n\t    BEGIN \n\t---------------------------------------------------- \n\t-- Print @DBName \n\t        SELECT  @cmd = 'Use ' + @DBName + '; ' \n\t        SELECT  @cmd = @cmd + ' Insert Into tempdb..TempResults \n\tSELECT @@ServerName AS ServerName, \n\tDB_NAME() AS DBName, \n\tobject_name(ddius.object_id) AS TableName , \n\tSUM(ddius.user_seeks \n\t+ ddius.user_scans \n\t+ ddius.user_lookups) AS Reads, \n\tSUM(ddius.user_updates) as Writes, \n\tSUM(ddius.user_seeks \n\t+ ddius.user_scans \n\t+ ddius.user_lookups \n\t+ ddius.user_updates) as ReadsWrites, \n\t(SELECT datediff(s,create_date, GETDATE()) \/ 86400.0 \n\tFROM sys.databases WHERE name = ''tempdb'') AS SampleDays, \n\t(SELECT datediff(s,create_date, GETDATE()) \n\tFROM sys.databases WHERE name = ''tempdb'') as SampleSeconds \n\tFROM sys.dm_db_index_usage_stats ddius \n\tINNER JOIN sys.indexes i\n\tON ddius.object_id = i.object_id \n\tAND i.index_id = ddius.index_id \n\tWHERE objectproperty(ddius.object_id,''IsUserTable'') = 1 --True \n\tAND ddius.database_id = db_id() \n\tGROUP BY object_name(ddius.object_id) \n\tORDER BY ReadsWrites DESC;' \n\t--PRINT @cmd \n\t        EXECUTE (@cmd) \n\t----------------------------------------------------- \n\t        FETCH NEXT FROM DBNameCursor INTO @DBName \n\t    END \n\tCLOSE DBNameCursor \n\tDEALLOCATE DBNameCursor \n\tSELECT  *\n\tFROM    tempdb..TempResults\n\tORDER BY DBName ,\n\t        TableName; \n\t--DROP TABLE tempdb..TempResults; \n<\/pre>\n
                  Listing 16: Read and write activity for all tables referenced since the last  server restart, in all databases<\/p>\n
                  Views<\/h3>\n
                  Views are scripted queries that are stored in the database. You can think of  them as virtual tables. Data is not stored in the view but we reference the view in our queries in exactly the same way  we would reference a table.<\/p>\n
                  In SQL Server, we can even, in some circumstances<\/a>, update data through a view. To  make a view read only, one trick is to use  SELECT DISTINCT<\/code> in the view  definition. A view is only updateable if each row in the view maps unambiguously to a single row in the underlying  table. Any view that fails this 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.<\/p>\n
                  \tSELECT  @@Servername AS ServerName ,\n\t        DB_NAME() AS DBName ,\n\t        o.name AS ViewName ,\n\t        o.[Type] ,\n\t        o.create_date\n\tFROM    sys.objects o\n\tWHERE   o.[Type] = 'V' -- View \n\tORDER BY o.NAME \n\t \n\t--OR \n\tSELECT  @@Servername AS ServerName ,\n\t        DB_NAME() AS DBName ,\n\t        Name AS ViewName ,\n\t        create_date\n\tFROM    sys.Views\n\tORDER BY Name \n\t--OR\n\tSELECT  @@Servername AS ServerName ,\n\t        TABLE_CATALOG ,\n\t        TABLE_SCHEMA ,\n\t        TABLE_NAME ,\n\t        TABLE_TYPE\n\tFROM    INFORMATION_SCHEMA.TABLES\n\tWHERE   TABLE_TYPE = 'VIEW'\n\tORDER BY TABLE_NAME \n\t--OR \n\t-- View details (Show the CREATE VIEW Code) \n\tSELECT  @@Servername AS ServerName ,\n\t        DB_NAME() AS DB_Name ,\n\t        o.name AS 'ViewName' ,\n\t        o.Type ,\n\t        o.create_date ,\n\t        sm.[DEFINITION] AS 'View script'\n\tFROM    sys.objects o\n\t        INNER JOIN sys.sql_modules sm ON o.object_id = sm.OBJECT_ID\n\tWHERE   o.Type = 'V' -- View \n\tORDER BY o.NAME;\n\tGO\n<\/pre>\n
                  Listing 17: Exploring views<\/p>\n
                  Synonyms<\/h3>\n
                  A synonym is an “also known as (aka)” name for an object in the database. A few  times in my career I have been asked to review a query only to be scratching my head trying to figure out the table to  which the query is referring. For example, consider a simple query  Select * from Client<\/code>. I search  for the table named  Client<\/code> but I can’t find it. OK, it must be a view then,  search for view named  Client<\/code> and I still can’t find  it. I must have the wrong database? Turns out  Client<\/code> is a  synonym for a customer and the actual table is  Customer<\/code>. The marketing group  wanted to refer to this table as Client so created a synonym. Thankfully, use of synonyms is rare, but they can cause  confusion if you are not aware of them.<\/p>\n
                  \t-- which synonyms exist?\n\tSELECT  @@Servername AS ServerName ,\n\t        DB_NAME() AS DBName ,\n\t        o.name AS ViewName ,\n\t        o.Type ,\n\t        o.create_date\n\tFROM    sys.objects o\n\tWHERE   o.[Type] = 'SN' -- Synonym \n\tORDER BY o.NAME;\n\t--OR \n\t-- synonymn details \n\tSELECT  @@Servername AS ServerName ,\n\t        DB_NAME() AS DBName ,\n\t        s.name AS synonyms ,\n\t        s.create_date ,\n\t        s.base_object_name\n\tFROM    sys.synonyms s\n\tORDER BY s.name;\n\tGO\n<\/pre>\n
                  Listing 18: Exploring synonyms<\/p>\n
                  Stored Procedures <\/h3>\n
                  A stored procedure is a group of script(s) that are compiled into a single  execution plan. We can use the catalog views to find out which stored procedures exist, what activity they perform, and  which tables they reference.<\/p>\n
                  \t-- Stored Procedures \n\tSELECT  @@Servername AS ServerName ,\n\t        DB_NAME() AS DBName ,\n\t        o.name AS StoredProcedureName ,\n\t        o.[Type] ,\n\t        o.create_date\n\tFROM    sys.objects o\n\tWHERE   o.[Type] = 'P' -- Stored Procedures \n\tORDER BY o.name\n\t--OR \n\t-- Stored Procedure details \n\tSELECT  @@Servername AS ServerName ,\n\t        DB_NAME() AS DB_Name ,\n\t        o.name AS 'ViewName' ,\n\t        o.[type] ,\n\t        o.Create_date ,\n\t        sm.[definition] AS 'Stored Procedure script'\n\tFROM    sys.objects o\n\t        INNER JOIN sys.sql_modules sm ON o.object_id = sm.object_id\n\tWHERE   o.[type] = 'P' -- Stored Procedures \n\t        -- AND sm.[definition] LIKE '%insert%'\n\t        -- AND sm.[definition] LIKE '%update%'\n\t        -- AND sm.[definition] LIKE '%delete%'\n\t        -- AND sm.[definition] LIKE '%tablename%'\n\tORDER BY o.name;\n\tGO\n<\/pre>\n
                  Listing 19: Exploring stored procedures<\/p>\n
                  With a simply addition to the  WHERE<\/code> clause of the stored  procedure details query, we can investigate, for example, only those stored procedures that perform inserts.<\/p>\n
                  \t...\n\tWHERE   o.[type]  = 'P' -- Stored Procedures \n\t        AND sm.definition LIKE '%insert%'\n\tORDER BY o.name\n\t...\n<\/pre>\n
                  Simply modify the  WHERE<\/code> clause as required to  investigate stored procedures that do updates (LIKE '%update%'<\/code>),  deletes (LIKE '%delete%'<\/code>), or reference  a particular table (LIKE '%tablename%'<\/code>).<\/p>\n
                  Functions<\/h3>\n
                  A function is stored SQL that accept parameters, performs an action or  calculation and returns a result.<\/p>\n
                  \t-- Functions \n\tSELECT  @@Servername AS ServerName ,\n\t        DB_NAME() AS DB_Name ,\n\t        o.name AS 'Functions' ,\n\t        o.[Type] ,\n\t        o.create_date\n\tFROM    sys.objects o\n\tWHERE   o.Type = 'FN' -- Function \n\tORDER BY o.NAME;\n\t--OR \n\t-- Function details \n\tSELECT  @@Servername AS ServerName ,\n\t        DB_NAME() AS DB_Name ,\n\t        o.name AS 'FunctionName' ,\n\t        o.[type] ,\n\t        o.create_date ,\n\t        sm.[DEFINITION] AS 'Function script'\n\tFROM    sys.objects o\n\t        INNER JOIN sys.sql_modules sm ON o.object_id = sm.OBJECT_ID\n\tWHERE   o.[Type] = 'FN' -- Function \n\tORDER BY o.NAME;\n\tGO\n<\/pre>\n
                  Listing 20: Exploring functions<\/p>\n
                  Triggers<\/h3>\n
                  A trigger is like a stored procedure that executes in response to a particular  event that occurs on the table to which the trigger belongs. For example, we can create   INSERT<\/code>,   UPDATE<\/code>  and   DELETE<\/code> triggers.<\/p>\n
                  \t-- Table Triggers \n\tSELECT  @@Servername AS ServerName ,\n\t        DB_NAME() AS DBName ,\n\t        parent.name AS TableName ,\n\t        o.name AS TriggerName ,\n\t        o.[Type] ,\n\t        o.create_date\n\tFROM    sys.objects o\n\t        INNER JOIN sys.objects parent ON o.parent_object_id = parent.object_id\n\tWHERE   o.Type = 'TR' -- Triggers \n\tORDER BY parent.name ,\n\t        o.NAME \n\t--OR \n\tSELECT  @@Servername AS ServerName ,\n\t        DB_NAME() AS DB_Name ,\n\t        Parent_id ,\n\t        name AS TriggerName ,\n\t        create_date\n\tFROM    sys.triggers\n\tWHERE   parent_class = 1\n\tORDER BY name;\n\t--OR \n\t-- Trigger Details \n\tSELECT  @@Servername AS ServerName ,\n\t        DB_NAME() AS DB_Name ,\n\t        OBJECT_NAME(Parent_object_id) AS TableName ,\n\t        o.name AS 'TriggerName' ,\n\t        o.Type ,\n\t        o.create_date ,\n\t        sm.[DEFINITION] AS 'Trigger script'\n\tFROM    sys.objects o\n\t        INNER JOIN sys.sql_modules sm ON o.object_id = sm.OBJECT_ID\n\tWHERE   o.Type = 'TR' -- Triggers \n\tORDER BY o.NAME;\n\tGO\n<\/pre>\n
                  Listing 21: Exploring triggers<\/p>\n
                  CHECK Constraints<\/h3>\n
                   CHECK<\/code>  constraints are a good way to implement business logic in a database. For example, certain fields must be positive or  negative or a date in one column must be later than a date in another column.<\/p>\n
                  \t-- Check Constraints \n\tSELECT  @@Servername AS ServerName ,\n\t        DB_NAME() AS DBName ,\n\t        parent.name AS 'TableName' ,\n\t        o.name AS 'Constraints' ,\n\t        o.[Type] ,\n\t        o.create_date\n\tFROM    sys.objects o\n\t        INNER JOIN sys.objects parent\n\t               ON o.parent_object_id = parent.object_id\n\tWHERE   o.Type = 'C' -- Check Constraints \n\tORDER BY parent.name ,\n\t        o.name \n\t--OR \n\t--CHECK constriant definitions\n\tSELECT  @@Servername AS ServerName ,\n\t        DB_NAME() AS DBName ,\n\t        OBJECT_SCHEMA_NAME(parent_object_id) AS SchemaName ,\n\t        OBJECT_NAME(parent_object_id) AS TableName ,\n\t        parent_column_id AS Column_NBR ,\n\t        Name AS CheckConstraintName ,\n\t        type ,\n\t        type_desc ,\n\t        create_date ,\n\t        OBJECT_DEFINITION(object_id) AS CheckConstraintDefinition\n\tFROM    sys.Check_constraints\n\tORDER BY TableName ,\n\t        SchemaName ,\n\t        Column_NBR \n\tGO\n<\/pre>\n
                  Listing 22: Exploring CHECK constraints<\/p>\n
                  Exploring your Data Model in depth<\/h2>\n
                  Up to this point, we’ve examined scripts that gives us a ‘high level’ view of  the objects that comprise our databases. Often though, we’ll want more in-depth knowledge of each table, including the  columns, their data types and any default values defined, which keys, constraints and indexes exist (or are missing) and  so on. <\/p>\n
                  The queries presented across the coming sections build on this foundation, and  provide a means almost to “reverse engineer” your existing data model.<\/p>\n
                  Columns<\/h3>\n
                  The following script documents the tables and their column definitions, in a  specified database. The resulting output is a good one to cut and paste into Excel, where you can filter or sort on  column names to get a good understanding of the data types that exist in a given database. Watch out for and question  column names that are the same but have different data types or different lengths. <\/p>\n
                  \t-- Table Columns \n\tSELECT  @@Servername AS Server ,\n\t        DB_NAME() AS DBName ,\n\t        isc.Table_Name AS TableName ,\n\t        isc.Table_Schema AS SchemaName ,\n\t        Ordinal_Position AS Ord ,\n\t        Column_Name ,\n\t        Data_Type ,\n\t        Numeric_Precision AS Prec ,\n\t        Numeric_Scale AS Scale ,\n\t        Character_Maximum_Length AS LEN , -- -1 means MAX like Varchar(MAX) \n\t        Is_Nullable ,\n\t        Column_Default ,\n\t        Table_Type\n\tFROM    INFORMATION_SCHEMA.COLUMNS isc\n\t        INNER JOIN information_schema.tables ist\n\t              ON isc.table_name = ist.table_name \n\t--      WHERE Table_Type = 'BASE TABLE' -- 'Base Table' or 'View' \n\tORDER BY DBName ,\n\t        TableName ,\n\t        SchemaName ,\n\t        Ordinal_position; \n\t \n\t-- Summary of Column names and usage counts \n\t-- Watch for column names with different data types or different lengths \n\tSELECT  @@Servername AS Server ,\n\t        DB_NAME() AS DBName ,\n\t        Column_Name ,\n\t        Data_Type ,\n\t        Numeric_Precision AS Prec ,\n\t        Numeric_Scale AS Scale ,\n\t        Character_Maximum_Length ,\n\t        COUNT(*) AS Count\n\tFROM    information_schema.columns isc\n\t        INNER JOIN information_schema.tables ist\n\t               ON isc.table_name = ist.table_name\n\tWHERE   Table_type = 'BASE TABLE'\n\tGROUP BY Column_Name ,\n\t        Data_Type ,\n\t        Numeric_Precision ,\n\t        Numeric_Scale ,\n\t        Character_Maximum_Length;\n\t \n\t-- Summary of data types \n\tSELECT  @@Servername AS ServerName ,\n\t        DB_NAME() AS DBName ,\n\t        Data_Type ,\n\t        Numeric_Precision AS Prec ,\n\t        Numeric_Scale AS Scale ,\n\t        Character_Maximum_Length AS [Length] ,\n\t        COUNT(*) AS COUNT\n\tFROM    information_schema.columns isc\n\t        INNER JOIN information_schema.tables ist\n\t               ON isc.table_name = ist.table_name\n\tWHERE   Table_type = 'BASE TABLE'\n\tGROUP BY Data_Type ,\n\t        Numeric_Precision ,\n\t        Numeric_Scale ,\n\t        Character_Maximum_Length\n\tORDER BY Data_Type ,\n\t        Numeric_Precision ,\n\t        Numeric_Scale ,\n\t        Character_Maximum_Length \n\t \n\t-- Large object data types or Binary Large Objects(BLOBs) \n\t-- Note if you are using Enterprise edition, these tables can't rebuild indexes \"Online\" \n\tSELECT  @@Servername AS ServerName ,\n\t        DB_NAME() AS DBName ,\n\t        isc.Table_Name ,\n\t        Ordinal_Position AS Ord ,\n\t        Column_Name ,\n\t        Data_Type AS BLOB_Data_Type ,\n\t        Numeric_Precision AS Prec ,\n\t        Numeric_Scale AS Scale ,\n\t        Character_Maximum_Length AS [Length]\n\tFROM    information_schema.columns isc\n\t        INNER JOIN information_schema.tables ist\n\t               ON isc.table_name = ist.table_name\n\tWHERE   Table_type = 'BASE TABLE'\n\t        AND ( Data_Type IN ( 'text', 'ntext', 'image', 'XML' )\n\t              OR ( Data_Type IN ( 'varchar', 'nvarchar', 'varbinary' )\n\t                   AND Character_Maximum_Length = -1\n\t                 )\n\t            ) -- varchar(max), nvarchar(max), varbinary(max) \n\tORDER BY isc.Table_Name ,\n\t        Ordinal_position;\n<\/pre>\n
                  Listing 23: Exploring columns and their data types<\/p>\n
                  Column Defaults <\/h4>\n
                  Column Defaults are values that are stored in the column, if no value is entered  for that column, when the record is first inserted. A common default for a column that stores dates is   getdate() or current_timestamp<\/code>.  Another common default in auditing is  system_user<\/code>, to identify the  login that performed a certain action.<\/p>\n
                  \t-- Table Defaults \n\tSELECT  @@Servername AS ServerName ,\n\t        DB_NAME() AS DBName ,\n\t        parent.name AS TableName ,\n\t        o.name AS Defaults ,\n\t        o.[Type] ,\n\t        o.Create_date\n\tFROM    sys.objects o\n\t        INNER JOIN sys.objects parent\n\t               ON o.parent_object_id = parent.object_id\n\tWHERE   o.[Type] = 'D' -- Defaults \n\tORDER BY parent.name ,\n\t        o.NAME\n\t \n\t--OR \n\t-- Column Defaults \n\tSELECT  @@Servername AS ServerName ,\n\t        DB_NAME() AS DB_Name ,\n\t        OBJECT_SCHEMA_NAME(parent_object_id) AS SchemaName ,\n\t        OBJECT_NAME(parent_object_id) AS TableName ,\n\t        parent_column_id AS Column_NBR ,\n\t        Name AS DefaultName ,\n\t        [type] ,\n\t        type_desc ,\n\t        create_date ,\n\t        OBJECT_DEFINITION(object_id) AS Defaults\n\tFROM    sys.default_constraints\n\tORDER BY TableName ,\n\t        Column_NBR \n\t--OR \n\t-- Column Defaults \n\tSELECT  @@Servername AS ServerName ,\n\t        DB_NAME() AS DB_Name ,\n\t        OBJECT_SCHEMA_NAME(t.object_id) AS SchemaName ,\n\t        t.Name AS TableName ,\n\t        c.Column_ID AS Ord ,\n\t        c.Name AS Column_Name ,\n\t        OBJECT_NAME(default_object_id) AS DefaultName ,\n\t        OBJECT_DEFINITION(default_object_id) AS Defaults\n\tFROM    sys.Tables t\n\t        INNER JOIN sys.columns c ON t.object_id = c.object_id\n\tWHERE   default_object_id <> 0\n\tORDER BY TableName ,\n\t        SchemaName ,\n\t        c.Column_ID \n\tGO\n<\/pre>\n
                  Listing 24: Exploring column default values<\/p>\n
                  Computed columns<\/h4>\n
                  Computed columns are columns where the values determined by an equation, usually  referencing other columns in the table.<\/p>\n
                  \t-- Computed columns \n\tSELECT  @@Servername AS ServerName ,\n\t        DB_NAME() AS DBName ,\n\t        OBJECT_SCHEMA_NAME(object_id) AS SchemaName ,\n\t        OBJECT_NAME(object_id) AS Tablename ,\n\t        Column_id ,\n\t        Name AS Computed_Column ,\n\t        [Definition] ,\n\t        is_persisted\n\tFROM    sys.computed_columns\n\tORDER BY SchemaName ,\n\t        Tablename ,\n\t        [Definition]; \n\t--Or \n\t-- Computed Columns \n\tSELECT  @@Servername AS ServerName ,\n\t        DB_NAME() AS DBName ,\n\t        OBJECT_SCHEMA_NAME(t.object_id) AS SchemaName,\n\t        t.Name AS TableName ,\n\t        c.Column_ID AS Ord ,\n\t        c.Name AS Computed_Column\n\tFROM    sys.Tables t\n\t        INNER JOIN sys.Columns c ON t.object_id = c.object_id\n\tWHERE   is_computed = 1\n\tORDER BY t.Name ,\n\t        SchemaName ,\n\t        c.Column_ID \n\tGO\n<\/pre>\n
                  Listing 25: Exploring computed columns<\/p>\n
                  Identity Columns <\/h4>\n
                   IDENTITY<\/code>  columns are populated with unique system controlled numbers. A common example is an order number, where each time an  order is entered into the system, SQL Server assigns to the   IDENTITY<\/code> column the next sequential number.<\/p>\n
                  \tSELECT  @@Servername AS ServerName ,\n\t        DB_NAME() AS DBName ,\n\t        OBJECT_SCHEMA_NAME(object_id) AS SchemaName ,\n\t        OBJECT_NAME(object_id) AS TableName ,\n\t        Column_id ,\n\t        Name AS IdentityColumn ,\n\t        Seed_Value ,\n\t        Last_Value\n\tFROM    sys.identity_columns\n\tORDER BY SchemaName ,\n\t        TableName ,\n\t        Column_id; \n\tGO\n<\/pre>\n
                  Listing 26: Exploring IDENTITY columns<\/p>\n
                  Keys and Indexes<\/h3>\n
                  As discussed earlier, a general best practice all tables should have a primary  key clustered index. As a second general best practice, foreign keys should have a supporting index on the same columns  as the foreign key. Foreign key indexes provide the most likely way that tables be joined together in multi-table  queries. Foreign key indexes are also important for performance when deleting records. <\/p>\n
                  What indexes exist?<\/h4>\n
                  To see which indexes exist on all tables in the current database.<\/p>\n
                  \tSELECT  @@Servername AS ServerName ,\n\t        DB_NAME() AS DB_Name ,\n\t        o.Name AS TableName ,\n\t        i.Name AS IndexName\n\tFROM    sys.objects o\n\t        INNER JOIN sys.indexes i ON o.object_id = i.object_id\n\tWHERE   o.Type = 'U' -- User table \n\t        AND LEFT(i.Name, 1) <> '_' -- Remove hypothetical indexes \n\tORDER BY o.NAME ,\n\t        i.name; \n\tGO\n<\/pre>\n
                  Listing 27: Exploring existing indexes<\/p>\n
                  Which indexes are missing?<\/h4>\n
                  The indexing related DMVs store statistics that SQL Server uses recommend  indexes that could offer performance benefits, based on previously executed queries.<\/p>\n
                  Do not add these indexes blindly. I would review and question each index  suggested. Included column my come with a high cost of maintaining duplicate data.<\/p>\n
                  \t-- Missing Indexes DMV Suggestions \n\tSELECT  @@ServerName AS ServerName ,\n\t        DB_NAME() AS DBName ,\n\t        t.name AS 'Affected_table' ,\n\t        ( LEN(ISNULL(ddmid.equality_columns, N'')\n\t              + CASE WHEN ddmid.equality_columns IS NOT NULL\n\t                          AND ddmid.inequality_columns IS NOT NULL THEN ','\n\t                     ELSE ''\n\t                END) - LEN(REPLACE(ISNULL(ddmid.equality_columns, N'')\n\t                                   + CASE WHEN ddmid.equality_columns\n\t                                                             IS NOT NULL\n\t                                               AND ddmid.inequality_columns\n\t                                                             IS NOT NULL\n\t                                          THEN ','\n\t                                          ELSE ''\n\t                                     END, ',', '')) ) + 1 AS K ,\n\t        COALESCE(ddmid.equality_columns, '')\n\t        + CASE WHEN ddmid.equality_columns IS NOT NULL\n\t                    AND ddmid.inequality_columns IS NOT NULL THEN ','\n\t               ELSE ''\n\t          END + COALESCE(ddmid.inequality_columns, '') AS Keys ,\n\t        COALESCE(ddmid.included_columns, '') AS [include] ,\n\t        'Create NonClustered Index IX_' + t.name + '_missing_'\n\t        + CAST(ddmid.index_handle AS VARCHAR(20)) \n\t        + ' On ' + ddmid.[statement] COLLATE database_default\n\t        + ' (' + ISNULL(ddmid.equality_columns, '')\n\t        + CASE WHEN ddmid.equality_columns IS NOT NULL\n\t                    AND ddmid.inequality_columns IS NOT NULL THEN ','\n\t               ELSE ''\n\t          END + ISNULL(ddmid.inequality_columns, '') + ')'\n\t        + ISNULL(' Include (' + ddmid.included_columns + ');', ';')\n\t                                                  AS sql_statement ,\n\t        ddmigs.user_seeks ,\n\t        ddmigs.user_scans ,\n\t        CAST(( ddmigs.user_seeks + ddmigs.user_scans )\n\t        * ddmigs.avg_user_impact AS BIGINT) AS 'est_impact' ,\n\t        avg_user_impact ,\n\t        ddmigs.last_user_seek ,\n\t        ( SELECT    DATEDIFF(Second, create_date, GETDATE()) Seconds\n\t          FROM      sys.databases\n\t          WHERE     name = 'tempdb'\n\t        ) SecondsUptime \n\t-- Select * \n\tFROM    sys.dm_db_missing_index_groups ddmig\n\t        INNER JOIN sys.dm_db_missing_index_group_stats ddmigs\n\t               ON ddmigs.group_handle = ddmig.index_group_handle\n\t        INNER JOIN sys.dm_db_missing_index_details ddmid\n\t               ON ddmig.index_handle = ddmid.index_handle\n\t        INNER JOIN sys.tables t ON ddmid.OBJECT_ID = t.OBJECT_ID\n\tWHERE   ddmid.database_id = DB_ID()\n\tORDER BY est_impact DESC;\n\tGO\n<\/pre>\n
                  Listing 28: Finding missing indexes<\/p>\n
                  Foreign Keys <\/h4>\n
                  Foreign Keys define table dependencies and control referential integrity between  multiple tables. In an Entity Relationship Diagram (ERD), the lines between the tables indicate the foreign keys.<\/p>\n
                  \t-- Foreign Keys \n\tSELECT  @@Servername AS ServerName ,\n\t        DB_NAME() AS DB_Name ,\n\t        parent.name AS 'TableName' ,\n\t        o.name AS 'ForeignKey' ,\n\t        o.[Type] ,\n\t        o.Create_date\n\tFROM    sys.objects o\n\t        INNER JOIN sys.objects parent ON o.parent_object_id = parent.object_id\n\tWHERE   o.[Type] = 'F' -- Foreign Keys \n\tORDER BY parent.name ,\n\t        o.name \n\t--OR \n\tSELECT  f.name AS ForeignKey ,\n\t        SCHEMA_NAME(f.SCHEMA_ID) AS SchemaName ,\n\t        OBJECT_NAME(f.parent_object_id) AS TableName ,\n\t        COL_NAME(fc.parent_object_id, fc.parent_column_id) AS ColumnName ,\n\t        SCHEMA_NAME(o.SCHEMA_ID) ReferenceSchemaName ,\n\t        OBJECT_NAME(f.referenced_object_id) AS ReferenceTableName ,\n\t        COL_NAME(fc.referenced_object_id, fc.referenced_column_id)\n\t                                              AS ReferenceColumnName\n\tFROM    sys.foreign_keys AS f\n\t        INNER JOIN sys.foreign_key_columns AS fc\n\t               ON f.OBJECT_ID = fc.constraint_object_id\n\t        INNER JOIN sys.objects AS o ON o.OBJECT_ID = fc.referenced_object_id\n\tORDER BY TableName ,\n\t        ReferenceTableName;\n\tGO\n<\/pre>\n
                  Listing 29: Exploring Foreign keys<\/p>\n
                  It will produce output similar to the following:<\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n\n
                  \n
                  ForeignKey <\/p>\n<\/td>\n
                  		\n
                  TableName <\/p>\n<\/td>\n
                  		\n
                  ColumnName <\/p>\n<\/td>\n
                  		\n
                  Reference<\/p>\n
                  TableName <\/p>\n<\/td>\n
                  		\n
                  Reference<\/p>\n
                  ColumnName <\/p>\n<\/td>\n<\/tr>\n
                  \n
                  FK__discounts__stor___286302EC <\/p>\n<\/td>\n
                  		\n
                  discounts <\/p>\n<\/td>\n
                  		\n
                  stor_id <\/p>\n<\/td>\n
                  		\n
                  stores <\/p>\n<\/td>\n
                  		\n
                  stor_id <\/p>\n<\/td>\n<\/tr>\n
                  \n
                  FK__employee__job_id__34C8D9D1 <\/p>\n<\/td>\n
                  		\n
                  employee <\/p>\n<\/td>\n
                  		\n
                  job_id <\/p>\n<\/td>\n
                  		\n
                  jobs <\/p>\n<\/td>\n
                  		\n
                  job_id <\/p>\n<\/td>\n<\/tr>\n
                  \n
                  FK__employee__pub_id__37A5467C <\/p>\n<\/td>\n
                  		\n
                  employee <\/p>\n<\/td>\n
                  		\n
                  pub_id <\/p>\n<\/td>\n
                  		\n
                  publishers <\/p>\n<\/td>\n
                  		\n
                  pub_id <\/p>\n<\/td>\n<\/tr>\n
                  \n
                  FK__pub_info__pub_id__300424B4 <\/p>\n<\/td>\n
                  		\n
                  pub_info <\/p>\n<\/td>\n
                  		\n
                  pub_id <\/p>\n<\/td>\n
                  		\n
                  publishers <\/p>\n<\/td>\n
                  		\n
                  pub_id <\/p>\n<\/td>\n<\/tr>\n
                  \n
                  FK__roysched__title___267ABA7A <\/p>\n<\/td>\n
                  		\n
                  roysched <\/p>\n<\/td>\n
                  		\n
                  title_id <\/p>\n<\/td>\n
                  		\n
                  titles <\/p>\n<\/td>\n
                  		\n
                  title_id <\/p>\n<\/td>\n<\/tr>\n
                  \n
                  FK__sales__stor_id__239E4DCF <\/p>\n<\/td>\n
                  		\n
                  sales <\/p>\n<\/td>\n
                  		\n
                  stor_id <\/p>\n<\/td>\n
                  		\n
                  stores <\/p>\n<\/td>\n
                  		\n
                  stor_id <\/p>\n<\/td>\n<\/tr>\n
                  \n
                  FK__sales__title_id__24927208 <\/p>\n<\/td>\n
                  		\n
                  sales <\/p>\n<\/td>\n
                  		\n
                  title_id <\/p>\n<\/td>\n
                  		\n
                  titles <\/p>\n<\/td>\n
                  		\n
                  title_id <\/p>\n<\/td>\n<\/tr>\n
                  \n
                  FK__titleauth__au_id__1DE57479 <\/p>\n<\/td>\n
                  		\n
                  titleauthor <\/p>\n<\/td>\n
                  		\n
                  au_id <\/p>\n<\/td>\n
                  		\n
                  authors <\/p>\n<\/td>\n
                  		\n
                  au_id <\/p>\n<\/td>\n<\/tr>\n
                  \n
                  FK__titleauth__title__1ED998B2 <\/p>\n<\/td>\n
                  		\n
                  titleauthor <\/p>\n<\/td>\n
                  		\n
                  title_id <\/p>\n<\/td>\n
                  		\n
                  titles <\/p>\n<\/td>\n
                  		\n
                  title_id <\/p>\n<\/td>\n<\/tr>\n
                  \n
                  FK__titles__pub_id__1A14E395 <\/p>\n<\/td>\n
                  		\n
                  titles <\/p>\n<\/td>\n
                  		\n
                  pub_id <\/p>\n<\/td>\n
                  		\n
                  publishers <\/p>\n<\/td>\n
                  		\n
                  pub_id<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
                  Missing Indexes that support Foreign Keys. <\/h4>\n
                  As a general best practice, it is recommended to have an index associated with  each foreign key. This facilitates faster table joins, which are typically joined on foreign key columns anyway. Indexes  on foreign keys also facilitate 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.<\/p>\n
                  \t-- Foreign Keys missing indexes \n\t-- Note this script only works for creating single column indexes. \n\t-- Multiple FK columns are out of scope for this script. \n\tSELECT  DB_NAME() AS DBName ,\n\t        rc.Constraint_Name AS FK_Constraint , \n\t-- rc.Constraint_Catalog AS FK_Database, \n\t-- rc.Constraint_Schema AS FKSch, \n\t        ccu.Table_Name AS FK_Table ,\n\t        ccu.Column_Name AS FK_Column ,\n\t        ccu2.Table_Name AS ParentTable ,\n\t        ccu2.Column_Name AS ParentColumn ,\n\t        I.Name AS IndexName ,\n\t        CASE WHEN I.Name IS NULL\n\t             THEN 'IF NOT EXISTS (SELECT * FROM sys.indexes\n\t                                    WHERE object_id = OBJECT_ID(N'''\n\t                  + RC.Constraint_Schema + '.' + ccu.Table_Name\n\t                  + ''') AND name = N''IX_' + ccu.Table_Name + '_'\n\t                  + ccu.Column_Name + ''') '\n\t                  + 'CREATE NONCLUSTERED INDEX IX_' + ccu.Table_Name + '_'\n\t                  + ccu.Column_Name + ' ON ' + rc.Constraint_Schema + '.'\n\t                  + ccu.Table_Name + '( ' + ccu.Column_Name\n\t                  + ' ASC ) WITH (PAD_INDEX = OFF, \n\t                                   STATISTICS_NORECOMPUTE = OFF,\n\t                                   SORT_IN_TEMPDB = ON, IGNORE_DUP_KEY = OFF,\n\t                                   DROP_EXISTING = OFF, ONLINE = ON);'\n\t             ELSE ''\n\t        END AS SQL\n\tFROM    information_schema.referential_constraints RC\n\t        JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu\n\t         ON rc.CONSTRAINT_NAME = ccu.CONSTRAINT_NAME\n\t        JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu2\n\t         ON rc.UNIQUE_CONSTRAINT_NAME = ccu2.CONSTRAINT_NAME\n\t        LEFT JOIN sys.columns c ON ccu.Column_Name = C.name\n\t                                AND ccu.Table_Name = OBJECT_NAME(C.OBJECT_ID)\n\t        LEFT JOIN sys.index_columns ic ON C.OBJECT_ID = IC.OBJECT_ID\n\t                                          AND c.column_id = ic.column_id\n\t                                          AND index_column_id = 1\n\t                                           -- index found has the foreign key\n\t                                          --  as the first column \n\t        LEFT JOIN sys.indexes i ON IC.OBJECT_ID = i.OBJECT_ID\n\t                                   AND ic.index_Id = i.index_Id\n\tWHERE   I.name IS NULL\n\tORDER BY FK_table ,\n\t        ParentTable ,\n\t        ParentColumn; \n\tGO\n<\/pre>\n
                  Listing 30: Finding missing Foreign Key indexes<\/p>\n
                  Object Dependencies<\/h3>\n
                  It depends…I’m sure you have heard that before. I will review three different  methods to ‘reverse engineer’ database dependencies. The first method use the stored procedure   sp_msdependencies<\/code>. The second  method uses the foreign key systems tables. The third method uses a CTE. <\/p>\n
                  sp_msdependencies<\/h4>\n
                   sp_msdependencies<\/code>  is a SQL Server undocumented stored procedure that can be helpful in navigating complex table interdependencies.  <\/p>\n
                  \tEXEC sp_msdependencies '?' -- Displays Help \n\t \n\tsp_MSobject_dependencies name = NULL, type = NULL, flags = 0x01fd\n\t  name:  name or null (all objects of type)\n\t  type:  type number (see below) or null\n\t         if both null, get all objects in database\n\t  flags is a bitmask of the following values:\n\t         0x10000  = return multiple parent\/child rows per object\n\t         0x20000  = descending return order\n\t         0x40000  = return children instead of parents\n\t         0x80000  = Include input object in output result set\n\t         0x100000 = return only firstlevel (immediate) parents\/children\n\t         0x200000 = return only DRI dependencies\n\t         power(2, object type number(s))  to return in results set:\n\t                0 (1        - 0x0001)     - UDF\n\t                1 (2        - 0x0002)     - system tables or MS-internal objects\n\t                2 (4        - 0x0004)     - view\n\t                3 (8        - 0x0008)     - user table\n\t                4 (16              - 0x0010)     - procedure\n\t                5 (32              - 0x0020)     - log\n\t                6 (64       - 0x0040)     - default\n\t                7 (128      - 0x0080)     - rule\n\t                8 (256      - 0x0100)     - trigger\n\t                12 (1024    - 0x0400) - uddt\n\t         shortcuts:\n\t                29   (0x011c) - trig, view, user table, procedure\n\t                448  (0x00c1) - rule, default, datatype\n\t                4606 (0x11fd) - all but systables\/objects\n\t                4607 (0x11ff) - all\n<\/pre>\n
                  Listing 31: sp_msdependencies help<\/p>\n
                  If we list all dependencies using  sp_msdependencies<\/code>, it will  return four columns:  Type<\/code>,  ObjName<\/code>,   Owner<\/code>  (Schema) and   Sequence<\/code>.<\/p>\n
                  Make special note of the Sequence number, this will start at 1 and will grow in  sequential order. The Sequence is the number of layers, or rows, of dependencies. <\/p>\n
                  I have used this method several times when asked to perform archiving or  deleting of data on some large database models. If you know the table dependencies, you have a road map of the order in  which you need to archive or delete records. Start with the table with the largest sequence number first, then work  backward from the largest number to the smallest number. Tables with the same sequence number can be removed at the same  time. This method does not violate any of the foreign key constraints, and thus allows you to move\/delete records  without temporarily dropping and rebuilding constraints. <\/p>\n
                  \tEXEC sp_msdependencies NULL    -- List all database dependencies \n\tEXEC sp_msdependencies NULL, 3 -- List table dependencies \n<\/pre>\n
                  Listing 32: using sp_msdependencies to view all dependencies<\/p>\n
                  \"2066-img139.jpg\"<\/p>\n
                  In SSMS, if you right click on a table name you can click on ‘View Dependencies’  and ‘Objects that depend on tablename’.<\/p>\n
                   \t\"2066-img13A.jpg\"<\/p>\n
                  We can see similar information from  sp_msdependencies<\/code> as follows:<\/p>\n
                  \t-- sp_MSdependencies sp_MSdependencies - First level only \n\t-- Objects that are dependent on the specified object \n\tEXEC sp_msdependencies N'Sales.Customer',null, 1315327 -- Change Table Name \n<\/pre>\n
                  Listing 33: using sp_msdependencies to view objects that depend on a table  (first level only)<\/p>\n
                   \t\"2066-img13B.jpg\"<\/p>\n
                  In SSMS if you right click on a table name you can click on ‘View Dependencies’  and ‘Objects that depend on tablename’, and then expand all the ‘+’ signs to see all the levels it looks like this.<\/p>\n
                   \t\"2066-img13C.jpg\"<\/p>\n
                  The following  msdependencies<\/code> report would  provide similar information.<\/p>\n
                  \t-- sp_MSdependencies - All levels \n\t-- Objects that are dependent on the specified object \n\tEXEC sp_MSdependencies N'Sales.Customer', NULL, 266751 -- Change Table Name \n<\/pre>\n
                  Listing 34: using sp_msdependencies to view objects that depend on a table (all  levels)<\/p>\n
                   \"2066-img13E.jpg\"<\/p>\n
                  Similarly, in SSMS, we can see the objects on which a given table depends.<\/p>\n
                   \"2066-img13F.jpg\"<\/p>\n
                  The following  msdependencies<\/code> report would  provide similar information.<\/p>\n
                  \t-- Objects that the specified object is dependent on \n\tEXEC sp_MSdependencies N'Sales.Customer', null, 1053183 -- Change Table \n<\/pre>\n
                  Listing 35: Using sp_msdependencies to view objects on which a table depends<\/p>\n
                   \"2066-img141.jpg\"<\/p>\n
                  If you want a list of just table dependencies you could use a temp table to  filter the dependency types.<\/p>\n
                  \tCREATE TABLE #TempTable1\n\t     (\n\t      Type INT ,\n\t      ObjName VARCHAR(256) ,\n\t      Owner VARCHAR(25) ,\n\t      Sequence INT\n\t    ); \n\tINSERT  INTO #TempTable1\n\t        EXEC sp_MSdependencies NULL \n\tSELECT  *\n\tFROM    #TempTable1\n\tWHERE   Type = 8 --Tables \n\tORDER BY Sequence ,\n\t        ObjName \n\tDROP TABLE #TempTable1; \n<\/pre>\n
                  Listing 36: Using sp_msdependencies to view only table dependencies<\/p>\n
                  Query the system catalog views<\/h4>\n
                  The second method to reverse engineer your database dependencies is to query the  foreign key relationships system tables.<\/p>\n
                  \t--Independent tables\n\tSELECT  Name AS InDependentTables\n\tFROM    sys.tables\n\tWHERE   object_id NOT IN ( SELECT referenced_object_id\n\t                             FROM   sys.foreign_key_columns )\n                                                -- Check for parents\n\t        AND object_id NOT IN ( SELECT parent_object_id\n\t                                 FROM   sys.foreign_key_columns )\n                                               -- Check for Dependents\n\tORDER BY Name\n\t \n\t-- Tables with dependencies.\n\tSELECT DISTINCT\n\t        OBJECT_NAME(referenced_object_id) AS ParentTable ,\n\t        OBJECT_NAME(parent_object_id) AS DependentTable ,\n\t        OBJECT_NAME(constraint_object_id) AS ForeignKeyName\n\tFROM    sys.foreign_key_columns\n\tORDER BY ParentTable ,\n\t        DependentTable\n\t \n\t-- Top level of the pyramid tables. Tables with no parents.\n\tSELECT DISTINCT\n\t        OBJECT_NAME(referenced_object_id) AS TablesWithNoParent\n\tFROM    sys.foreign_key_columns\n\tWHERE   referenced_object_id NOT IN ( SELECT  parent_object_id\n\t                                        FROM    sys.foreign_key_columns )\n\tORDER BY 1\n\t \n\t-- Bottom level of the pyramid tables. \n\t-- Tables with no dependents. (These are the leaves on a tree.)\n\tSELECT DISTINCT\n\t        OBJECT_NAME(parent_object_id) AS TablesWithNoDependents\n\tFROM    sys.foreign_key_columns\n\tWHERE   parent_object_id NOT IN ( SELECT  referenced_object_id\n\t                                    FROM    sys.foreign_key_columns )\n\tORDER BY 1\n\t \n\t-- Tables with both parents and dependents. \n\t-- Tables in the middle of the hierarchy\n\tSELECT DISTINCT\n\t        OBJECT_NAME(referenced_object_id) AS MiddleTables\n\tFROM    sys.foreign_key_columns\n\tWHERE   referenced_object_id IN ( SELECT  parent_object_id\n\t                                    FROM    sys.foreign_key_columns )\n\t        AND parent_object_id NOT IN ( SELECT  referenced_object_id\n\t                                        FROM    sys.foreign_key_columns )\n\tORDER BY 1;\n\t \n\t-- in rare cases, you might find a self-referencing dependent table.\n\t-- Recursive (self) referencing table dependencies. \n\tSELECT DISTINCT\n\t        OBJECT_NAME(referenced_object_id) AS ParentTable ,\n\t        OBJECT_NAME(parent_object_id) AS ChildTable ,\n\t        OBJECT_NAME(constraint_object_id) AS ForeignKeyName\n\tFROM    sys.foreign_key_columns\n\tWHERE   referenced_object_id = parent_object_id\n\tORDER BY 1 ,\n\t        2;\n<\/pre>\n
                  Listing 37: Using catalog views to view dependencies<\/p>\n
                  Using a Common Table Expression (CTE)<\/h4>\n
                  The third method to reverse engineer your database hierarchical dependencies is  to solve a recursive query using a Common Table Expression (CTE).<\/p>\n
                  \t-- How to find the hierarchical dependencies\n\t-- Solve recursive queries using Common Table Expressions (CTE)\n\tWITH    TableHierarchy ( ParentTable, DependentTable, Level )\n\t          AS (\n\t-- Anchor member definition (First level group to start the process)\n\t               SELECT DISTINCT\n\t                        CAST(NULL AS INT) AS ParentTable ,\n\t                        e.referenced_object_id AS DependentTable ,\n\t                        0 AS Level\n\t               FROM     sys.foreign_key_columns AS e\n\t               WHERE    e.referenced_object_id NOT IN (\n\t                        SELECT  parent_object_id\n\t                        FROM    sys.foreign_key_columns )\n\t-- Add filter dependents of only one parent table\n\t-- AND Object_Name(e.referenced_object_id) = 'User'\n\t               UNION ALL\n\t-- Recursive member definition (Find all the layers of dependents)\n\t               SELECT --Distinct\n\t                        e.referenced_object_id AS ParentTable ,\n\t                        e.parent_object_id AS DependentTable ,\n\t                        Level + 1\n\t               FROM     sys.foreign_key_columns AS e\n\t                        INNER JOIN TableHierarchy AS d\n                               ON ( e.referenced_object_id ) = \n                                                      d.DependentTable\n\t             )\n\t    -- Statement that executes the CTE\n\tSELECT DISTINCT\n\t        OBJECT_NAME(ParentTable) AS ParentTable ,\n\t        OBJECT_NAME(DependentTable) AS DependentTable ,\n\t        Level\n\tFROM    TableHierarchy\n\tORDER BY Level ,\n\t        ParentTable ,\n\t        DependentTable;\n<\/pre>\n
                  Listing 38: Using catalog views and a CTE to view dependencies<\/p>\n
                  Summary<\/h2>\n
                  Within an hour or two, I can usually gain a good understanding of any database  design, using the ‘reverse engineering’ methods described in this article. <\/p>\n
                  My intention has been to provide a set of sample scripts that you can run  immediately on the server and databases you are currently supporting. Microsoft Excel is a great tool to use to help  analyze and document your database data. I recommend copying some of the following tables and column query results to  excel, so you can filter and sort in a variety of ways. It also is a great way to share the results of your reverse  engineering analysis with your managers and peers.<\/p>\n<\/p><\/div>\n","protected":false},"excerpt":{"rendered":"
                  Most DBAs hoard their own favourite T-SQL scripts to help them with their work, often on a USB ‘thumbdrive’, but it is rare that one of them offers us a glimpse of the sort of scripts that they find useful. It is our good fortune that Scott Swanberg shows us those scripts he uses for discovering more about database objects.…<\/p>\n","protected":false},"author":189815,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143527],"tags":[4168,4170,4150,4151,4183],"coauthors":[],"class_list":["post-1881","post","type-post","status-publish","format-standard","hentry","category-database-administration-sql-server","tag-database","tag-database-administration","tag-sql","tag-sql-server","tag-t-sql"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1881","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/189815"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=1881"}],"version-history":[{"count":5,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1881\/revisions"}],"predecessor-version":[{"id":92222,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1881\/revisions\/92222"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=1881"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=1881"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=1881"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=1881"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}