Stairway to Exploring Database Metadata

Exploring Facts About SQL Server Tables: Stairway to Exploring Database Metadata Level 5

,

Now that we’ve explored, in preceding levels, some of the information that is available about indexes, triggers, keys and distribution statistics, we can concentrate on the tables themselves and their columns.

References to and from tables

Tables can be referenced by other objects such as views and stored procedures. The following code shows us what objects reference the table HumanResources.employee in AdventureWorks2012. (All our examples were tested on this database).

SELECT
coalesce(object_schema_name(Referencing_ID)+'.','')+ --likely schema name
    object_name(Referencing_ID)+ --definite entity name
    coalesce('.'+col_name(referencing_ID,referencing_minor_id),'') AS [referencing],
referencing_minor_ID
FROM sys.sql_expression_dependencies
  INNER JOIN sys.objects o
  ON referencing_ID=o.object_ID
WHERE referenced_id =object_id('HumanResources.employee')
  AND is_schema_bound_reference=0

We can also see what is referenced by any child object of a table, by which I mean objects such as triggers and check constraints,

SELECT
  coalesce(Referenced_server_name+'.','')+ --possible server name if cross-server
       coalesce(referenced_database_name+'.','')+ --possible database name if cross-database
       coalesce(referenced_schema_name+'.','')+ --likely schema name
       coalesce(referenced_entity_name,'') + --very likely entity name
       coalesce('.'+col_name(referenced_ID,referenced_minor_id),'')AS [referenced],
convert(varchar(128),coalesce(object_schema_name(Referencing_ID)+'.','')+ --likely schema name
    object_name(Referencing_ID)+ --definite entity name
    coalesce('.'+col_name(referencing_ID,referencing_minor_id),''))
+' ('+ lower(replace(type_desc,'_',' '))+')' AS [referencing]
    FROM sys.sql_expression_dependencies
  INNER JOIN sys.objects o
  ON referencing_ID=o.object_ID
WHERE parent_object_id=object_id('HumanResources.employee')

We are only looking at references in code here, including the code within check constraints, rather than those references defined by foreign key constraints. If we perform the same query but using a table with a complex trigger, such as ‘ales.SalesOrderDetail we can find some surprising dependencies.

As you can see, tables can reference other tables, via a trigger in this case, but you’ll notice that these code references (called ‘soft’ references) are not the only type. There are these ‘soft’ dependencies; references to other objects in SQL code that are exposed by sys.sql_expression_dependencies, and ‘hard’ dependencies that are exposed by the object catalog views. ‘Hard’ dependencies are inherent in the structure of the database, whereas code can reference objects in another database on the same server or on another server. If foreign key constraints have been properly added to enforce the integrity of these references, we can determine this network of dependencies. If, for example we wanted to find out what tables HumanResources.employee references, and the tables that reference it, we would use this code…

SELECT
    object_schema_name(parent_object_ID)+'.'
   +object_name(parent_object_ID)AS referrer,
    object_schema_name(referenced_object_ID)+'.'
   +object_name(referenced_object_ID) AS referenced
FROM sys.foreign_keys
WHERE parent_object_ID = object_id('HumanResources.employee')
   OR referenced_object_ID = object_id('HumanResources.employee')

But if you link tables via foreign key references that aren’t enforced by foreign key constraints, then you can’t detect these references this way, and you can only determine them if a procedure, view or function links the two tables.

Getting the properties of tables

Here is a statement that gives a summary of the main features of your database’s tables, providing the following information

  • indexes: whether it has an index of any type, a clustered index, a primary key, a nonclustered index, an active full-text index
  • constraints: Whether there is a CHECK constraint, a UNIQUE constraint, a DEFAULT constraint, or a FOREIGN KEY constraint. It also tells you if the table is referenced by a FOREIGN KEY constraint.
  • triggers Whether the table has an INSERT trigger, an UPDATE trigger or a DELETE trigger.
  • special types of columns: whether the table has an identity column. a ROWGUIDCOL for a uniqueidentifier, any legacy large object (text, ntext, or image) column or a timestamp column.
SELECT Object_Schema_name(t.object_ID)+'.'+t.name  AS [Qualified Name],
--questions about indexes
  CASE WHEN OBJECTPROPERTYEX(object_id,'TableHasIndex') = 0  
THEN 'no' ELSE 'yes' END AS  [Any index],--Table has an index of any type. 
  CASE WHEN OBJECTPROPERTYEX(object_id,'TableHasClustIndex') = 0  
THEN 'no' ELSE 'yes' END AS  [Clustered Index],--Table has a clustered index.
  CASE WHEN OBJECTPROPERTYEX(object_id,'TableHasPrimaryKey') = 0  
THEN 'no' ELSE 'yes' END AS  [Primary Key],--Table has a primary key
  CASE WHEN OBJECTPROPERTYEX(object_id,'TableHasNonclustIndex') = 0  
THEN 'no' ELSE 'yes' END AS  [nonCl Index],--Table has a nonclustered index.
  CASE WHEN OBJECTPROPERTYEX(object_id,'TableHasActiveFulltextIndex') = 0  
THEN 'no' ELSE 'yes' END AS  [FT index],--Table has an active full-text index.
 --questions about constraints
  CASE WHEN OBJECTPROPERTYEX(object_id,'TableHasCheckCnst') = 0  
  THEN 'no' ELSE 'yes' END AS  [Check Cnst],--Table has a CHECK constraint.
  CASE WHEN OBJECTPROPERTYEX(object_id,'TableHasUniqueCnst') = 0  
THEN 'no' ELSE 'yes' END AS  [Unique Cnst],--Table has a UNIQUE constraint.
  CASE WHEN OBJECTPROPERTYEX(object_id,'TableHasDefaultCnst') = 0  
THEN 'no' ELSE 'yes' END AS  [Default Cnst],--Table has a DEFAULT constraint.
  CASE WHEN OBJECTPROPERTYEX(object_id,'TableHasForeignKey') = 0  
THEN 'no' ELSE 'yes' END AS  [FK Cnst],--Table has a FOREIGN KEY constraint.
  CASE WHEN OBJECTPROPERTYEX(object_id,'TableHasForeignRef') = 0  
THEN 'no' ELSE 'yes' END AS  [FK Ref],--Table is referenced by a FOREIGN KEY constraint.
--questions about triggers
  CASE WHEN OBJECTPROPERTYEX(object_id,'TableHasInsertTrigger') = 0  
THEN 'no' ELSE 'yes' END AS  [Insert Tgr],--Object has an INSERT trigger.
  CASE WHEN OBJECTPROPERTYEX(object_id,'TableHasUpdateTrigger') = 0  
THEN 'no' ELSE 'yes' END AS  [Update Tgr],--Table has an UPDATE trigger.
  CASE WHEN OBJECTPROPERTYEX(object_id,'TableHasDeleteTrigger') = 0  
THEN 'no' ELSE 'yes' END AS  [Delete Tgr],--Table has a DELETE trigger.
--questions about types of columns
   CASE WHEN OBJECTPROPERTYEX(object_id,'TableHasIdentity') = 0  
THEN 'no' ELSE 'yes' END AS  [Identity Col],--Table has an identity column.
  CASE WHEN OBJECTPROPERTYEX(object_id,'TableHasRowGuidCol') = 0  
THEN 'no' ELSE 'yes' END AS  [ROWGUIDCOL],--has a ROWGUIDCOL for a uniqueidentifier col.
  CASE WHEN OBJECTPROPERTYEX(object_id,'TableHasTextImage') = 0  
THEN 'no' ELSE 'yes' END AS  [Has Lob],--Table has a text, ntext, or image column.
  CASE WHEN OBJECTPROPERTYEX(object_id,'TableHasTimestamp') = 0  
THEN 'no' ELSE 'yes' END AS  [Timestamp]--Table has a timestamp column.
  FROM sys.tables t
ORDER BY [Qualified Name]

Finding out about table columns

You can determine the columns and their datatypes quite easily from queries accessing the catalog views.

For example, if you want to quickly find out about the columns of the person.contact table, (or person.person in later versions of AdventureWorks) you can run this statement, to get a quick summary.

SELECT
  coalesce(object_schema_name(obj.object_ID)+'.','')+obj.name as ItsName,
  col.name+' '+t.name+ --now we get the datatype's details
  CASE 
    WHEN t.name IN ('char', 'varchar','nchar','nvarchar') 
  THEN '('+ --we fetch the length of the data
    CASE WHEN col.max_length=-1 THEN 'MAX' 
ELSE convert(VARCHAR(4), --get the length
  CASE WHEN t.name IN ('nchar','nvarchar') 
  THEN  col.max_length/2 ELSE col.max_length END ) 
    END+')'
WHEN t.name IN ('decimal','numeric') --we need scale and precision
      THEN '('+ convert(VARCHAR(4),col.precision)+','
      + convert(VARCHAR(4),col.Scale)+')'
ELSE ''
--now all we have to do is to get the column's extended properties
  END+ coalesce (' /* '+convert(varchar(128),ep.value)+ ' */','') as TheColumn
FROM sys.all_objects obj -- from all the objects (system and database)
 INNER JOIN sys.all_columns col --to get all the columns
  ON col.object_ID=obj.object_ID
 INNER JOIN sys.types t --to get the details of the types
  ON col.user_type_id=t.user_type_id
 LEFT OUTER JOIN sys.extended_properties ep --and the documentation
  ON col.object_id = ep.major_ID  
   AND col.column_ID = minor_ID AND class=1
WHERE obj.object_ID=object_id('person.contact')
ORDER BY column_ID

You will immediately see from this listing the great advantage of having extended properties set properly on columns!

I’ve written this as a general column-lister for any table, view or table-valued function. If you need to see what is in sys.tables, for example, you can use the same query.

Finding which tables have a particular column

So how do you find what tables have a particular column name? Let’s see what tables contain a column called ProductID.

SELECT
  OBJECT_SCHEMA_NAME(o.Object_ID)+'.'+OBJECT_NAME(o.Object_ID)
    +' ('+ LOWER(REPLACE(o.type_desc,'_',' '))+') '
+ COALESCE (' /* '+CONVERT(VARCHAR(128),ep.value)+ ' */','') AS TheObject
FROM sys.All_Columns c
  INNER JOIN sys.all_Objects o
  ON c.object_ID=o.object_ID
LEFT OUTER JOIN sys.extended_properties ep --and the documentation
  ON c.object_id = ep.major_ID  
   AND c.column_ID = minor_ID AND class=1
 WHERE c.name LIKE 'ProductID'

I’ve added the type of object as a convenience. After all it isn’t just tables that have columns!

Finding potential problems in tables

Tables can have a number of problems that can potentially present performance difficulties. It is easy to search your tables for possible issues.

The tables that are wide (more than 15 in this example; you can modify to taste):

SELECT OBJECT_SCHEMA_NAME(t.object_id) + '.' + OBJECT_NAME(t.object_id) AS TheTable,
  CONVERT(VARCHAR(5), COUNT(*)) + ' columns wide (more than 15 columns)' AS smell
  FROM sys.columns c INNER JOIN sys.tables t ON c.object_id = t.object_id
  GROUP BY t.object_id
  HAVING COUNT(*) > 15;

Tables that are heaps:

SELECT DISTINCT OBJECT_SCHEMA_NAME(t.Object_ID) + '.'
                + OBJECT_NAME(t.Object_ID) AS TheTable, 'heap' AS smell
  FROM sys.indexes /* see whether the table is a heap */    INNER JOIN sys.tables t ON t.object_ID = sys.indexes.object_ID
  WHERE sys.indexes.type = 0;

Tables that are not documented with extended properties;

SELECT OBJECT_SCHEMA_NAME(Object_ID) + '.' + OBJECT_NAME(Object_ID) AS TheTable,
  'Undocumented table' AS smell
  FROM sys.objects s /* it has no extended properties */    LEFT OUTER JOIN sys.extended_properties ep
                       ON s.object_ID = ep.major_ID AND minor_ID = 0
  WHERE type_desc = 'USER_TABLE' AND ep.value IS NULL;

Tables without a Primary Key:

SELECT OBJECT_SCHEMA_NAME(Object_ID) + '.' + OBJECT_NAME(Object_ID) AS TheTable,
  'No primary key' AS smell
  FROM sys.tables /* see whether the table has a primary key */  WHERE OBJECTPROPERTY(OBJECT_ID, 'TableHasPrimaryKey') = 0;

Tables with no indexes at all:

SELECT OBJECT_SCHEMA_NAME(Object_ID) + '.' + OBJECT_NAME(Object_ID) AS TheTable,
  'No index at all' AS smell
  FROM sys.tables /* see whether the table has any index */  WHERE OBJECTPROPERTY(OBJECT_ID, 'TableHasIndex') = 0;

Tables with no candidate key (unique constraint on column(s)):

SELECT OBJECT_SCHEMA_NAME(Object_ID) + '.' + OBJECT_NAME(Object_ID) AS TheTable,
  'No candidate key' AS smell
  FROM sys.tables /* if no unique constraint then it isn't relational */  WHERE OBJECTPROPERTY(OBJECT_ID, 'TableHasUniqueCnst') = 0;

Tables with disabled Index(es):

SELECT OBJECT_SCHEMA_NAME(Object_ID) + '.' + OBJECT_NAME(Object_ID) AS TheTable,
  'disabled Index(es)' AS smell
  FROM sys.indexes /* don't leave these lying around */  WHERE is_disabled = 1;

Tables with disabled constraint(s):

SELECT OBJECT_SCHEMA_NAME(Parent_Object_ID) + '.'
       + OBJECT_NAME(Parent_Object_ID) AS TheTable,
  'disabled constraint(s)' AS smell
  FROM sys.check_constraints /* hmm. i wonder why */  WHERE is_disabled = 1;

Tables with untrusted constraint(s):

SELECT OBJECT_SCHEMA_NAME(Parent_Object_ID) + '.'
       + OBJECT_NAME(Parent_Object_ID) AS TheTable,
  'untrusted constraint(s)' AS smell
  FROM sys.check_constraints /* ETL gone bad? */  WHERE is_not_trusted = 1;

Tables with a disabled Foreign Key(s):

SELECT OBJECT_SCHEMA_NAME(Parent_Object_ID) + '.'
       + OBJECT_NAME(Parent_Object_ID) AS TheTable, 'disabled FK' AS smell
  FROM sys.foreign_keys /* build script gone bad? */  WHERE is_disabled = 1;

Tables with untrusted Foreign Key(s):

SELECT OBJECT_SCHEMA_NAME(Parent_Object_ID) + '.'
       + OBJECT_NAME(Parent_Object_ID) AS TheTable, 'untrusted FK' AS smell
  FROM sys.foreign_keys /* Why do you have untrusted FKs?       
      Constraint was enabled without checking existing rows;
      therefore, the constraint may not hold for all rows. */  WHERE is_not_trusted = 1;

Tables unrelated to any other table:

SELECT OBJECT_SCHEMA_NAME(Object_ID) + '.' + OBJECT_NAME(Object_ID) AS TheTable,
  'unrelated to any other table' AS smell
  FROM sys.tables /* found a simpler way! */  WHERE OBJECTPROPERTYEX(OBJECT_ID, 'TableHasForeignKey') = 0
    AND OBJECTPROPERTYEX(OBJECT_ID, 'TableHasForeignRef') = 0;

Tables with unintelligible column names:

SELECT DISTINCT OBJECT_SCHEMA_NAME(Object_ID) + '.' + OBJECT_NAME(Object_ID) AS TheTable,
  'unintelligible column names' AS smell
  FROM sys.columns /* column names with no letters in them */  WHERE name COLLATE Latin1_General_CI_AI NOT LIKE '%[A-Z]%' COLLATE Latin1_General_CI_AI;

Tables with a foreign key that has no index:

SELECT OBJECT_SCHEMA_NAME(keys.Parent_Object_ID) + '.'
       + OBJECT_NAME(keys.Parent_Object_ID) AS TheTable,
  'foreign key ' + keys.Name + ' has no index' AS smell
  FROM sys.foreign_keys keys
    INNER JOIN sys.foreign_key_columns TheColumns
      ON keys.Object_ID = constraint_object_id
    LEFT OUTER JOIN sys.index_columns ic
      ON ic.object_ID = TheColumns.parent_Object_Id
     AND ic.column_ID = TheColumns.parent_Column_Id
     AND TheColumns.constraint_column_ID = ic.key_ordinal
  WHERE ic.object_ID IS NULL;

Tables with a GUID in a clustered Index:

SELECT OBJECT_SCHEMA_NAME(Ic.Object_ID) + '.' + OBJECT_NAME(Ic.Object_ID) AS TheTable,
  COL_NAME(Ic.Object_Id, Ic.Column_Id) + ' is a GUID in a clustered index' AS smell /* GUID in a clusterd IX */  FROM Sys.Index_Columns AS Ic
    INNER JOIN sys.columns c
      ON c.object_ID = Ic.object_ID AND c.column_ID = Ic.column_ID
    INNER JOIN sys.types t
      ON t.system_type_id = c.system_type_id
    INNER JOIN sys.indexes i
      ON i.object_ID = Ic.object_ID AND i.index_ID = Ic.index_ID
  WHERE t.name = 'uniqueidentifier'
    AND type_desc = 'CLUSTERED'
    AND OBJECTPROPERTY(Ic.OBJECT_ID, 'IsSystemTable') = 0;

Tables with non-compliant column names:

SELECT DISTINCT OBJECT_SCHEMA_NAME(Object_ID) + '.' + OBJECT_NAME(Object_ID) AS TheTable,
  'non-compliant column names' AS smell
  FROM sys.columns /* column names that need delimiters*/  WHERE name COLLATE Latin1_General_CI_AI LIKE '%[^_@$#A-Z0-9]%' COLLATE Latin1_General_CI_AI;

Tables with a trigger that doesn’t set NOCOUNT ON:

/* Triggers lacking `SET NOCOUNT ON`, which can cause unexpected results when INSERT statements subsequently use the OUTPUT clause */SELECT OBJECT_SCHEMA_NAME(ta.Object_ID) + '.' + OBJECT_NAME(ta.Object_ID) AS TheTable,
  'This table''s trigger, ' + OBJECT_NAME(tr.object_ID)
  + ', hasn’’t got NOCOUNT ON' AS smell
  FROM sys.tables ta /* see whether the table has any index */    INNER JOIN sys.triggers tr ON tr.parent_ID = ta.object_ID
    INNER JOIN sys.sql_modules mo
      ON tr.object_ID = mo.object_ID
  WHERE definition NOT LIKE '%set nocount on%';

Tables that are not referenced by any procedure, view or function:

SELECT OBJECT_SCHEMA_NAME(Object_ID) + '.' + OBJECT_NAME(Object_ID) AS TheTable,
  'not referenced by procedure, view or function' AS smell
  FROM sys.tables /* found a simpler way! */    LEFT OUTER JOIN sys.sql_expression_dependencies
                    ON referenced_id = sys.tables.object_id
  WHERE referenced_id IS NULL;

Tables with a disabled trigger:

SELECT DISTINCT OBJECT_SCHEMA_NAME(Parent_ID) + '.' + OBJECT_NAME(Parent_ID) ) AS TheTable,
  'has a disabled trigger' AS smell
  FROM sys.triggers
  WHERE is_disabled = 1 AND parent_ID > 0;

Tables that can't be indexed:

SELECT OBJECT_SCHEMA_NAME(Object_ID) + '.' + OBJECT_NAME(Object_ID) ) AS TheTable,
  'can''t be indexed' AS smell
  FROM sys.tables 
  WHERE OBJECTPROPERTY(OBJECT_ID, 'IsIndexable') = 0;

Table has unique constraint that is NULLable:

SELECT DISTINCT OBJECT_SCHEMA_NAME(keys.Parent_Object_ID) + '.'
                + OBJECT_NAME(keys.Parent_Object_ID) AS TheTable,
'has a unique unique constraint on a NULLable column' AS smell
  FROM sys.Key_Constraints keys
    INNER JOIN sys.Index_columns TheColumns
      ON keys.Parent_Object_ID = TheColumns.Object_ID
     AND unique_index_ID = index_ID
    INNER JOIN sys.columns c
      ON TheColumns.object_ID = c.object_ID
     AND TheColumns.column_ID = c.column_ID
  WHERE type = 'UQ' AND is_nullable = 1;

Getting information on table keys and indexes

Sometimes, you need to generate a report of the number of the various types of indexes and keys associated with a table. Here is a query that provides the total number of indexes, and provides the number of this total that are unique indexes, unique keys, primary keys, clustered indexes and so on, using the object catalog views.

SELECT OBJECT_SCHEMA_NAME(a.object_ID) + '.' + OBJECT_NAME(a.object_ID) AS [Table],
  SUM(CASE WHEN a.name IS NULL THEN 0 ELSE 1 END) AS [indexes], 
  SUM(CASE WHEN a.is_unique <> 0 THEN 1 ELSE 0 END) AS Unique_indexes,
  SUM(CASE WHEN a.is_unique_constraint <> 0 THEN 1 ELSE 0 END) AS [Unique Key], 
  SUM(CASE WHEN a.is_primary_key <> 0 THEN 1 ELSE 0 END ) AS [Primary Key],
  SUM(CASE WHEN a.type = 1 THEN 1 ELSE 0 END ) AS [Clustered],
  SUM(CASE WHEN a.type = 2 THEN 1 ELSE 0 END ) AS [Non-clustered],
  SUM(CASE WHEN a.type = 3 THEN 1 ELSE 0 END ) AS [XML], 
  SUM(CASE WHEN a.type = 4 THEN 1 ELSE 0 END ) AS [Spatial],
  SUM(CASE WHEN a.type = 5 THEN 1 ELSE 0 END ) AS [Clustered Columnstore],
  SUM(CASE WHEN a.type = 6 THEN 1 ELSE 0 END ) AS [Nonclustered Columnstore]
  FROM sys.indexes a
    INNER JOIN sys.tables ON a.object_ID = sys.tables.object_id
  WHERE OBJECT_SCHEMA_NAME(a.object_ID) <> 'sys'
  -- and a.name is not null
  GROUP BY a.object_ID;

Using a similar technique, you can get some valuable information about columns in your tables: how many columns there are, their nullability, how many are computed, replicated, sparse and so on.

SELECT OBJECT_SCHEMA_NAME(c.object_ID) 
          + '.' + OBJECT_NAME(c.object_ID) AS [Table],
  MAX(column_ID) AS [total], SUM(CONVERT(INT, c.is_nullable)) AS [Nullable],
  SUM(CONVERT(INT, c.is_computed)) AS [computed],
  SUM(CONVERT(INT, c.is_replicated)) AS [replicated],
  SUM(CONVERT(INT, c.is_sparse)) AS [sparse],
  SUM(CONVERT(INT, c.is_xml_document)) AS [XML Doc]
  FROM sys.columns c INNER JOIN sys.tables t ON c.object_ID = t.object_id
  GROUP BY c.Object_id
  ORDER BY total DESC;

Determining dependency in tables

Sometimes, it is important to do a series of operations on a group of tables in a specific order for that operation. If, for example, you need to read data into tables after you’ve done a database build, then the simplest way is to read the data into the tables that do not reference any other tables first. Then you read in the tables that reference only the tables whose data you’ve loaded in. Then you just keep repeating this, checking that you don’t read a table twice. Eventually, you either hit a reference that can’t be resolved, (in which you’d need another slower technique), or you’ve got an excellent fast way of populating a database.

The following batch produces a result that has the tables in the right order for the insertion of data, and you can use it for any operation that needs to be in a similar dependency order. (Deleting tables would be in the reverse order to this!)

SET NOCOUNT ON;
DECLARE @Rowcount INT, @ii INT;
CREATE TABLE #tables
  (
    TheObject_ID INT, --the tables' object ID
    TheName SYSNAME, --the name of the table
    TheSchema SYSNAME, --the schema where it lives
    HasIdentityColumn INT, --whether it has an identity column
    TheOrder INT DEFAULT 0
); --we update this later to impose an order
--let's do a topological sort to create the right dependency order
-- (For background, see https://en.wikipedia.org/wiki/Topological_sorting)
--first we read in all the tables from the database.
INSERT INTO #tables(TheObject_ID, TheName, TheSchema, HasIdentityColumn)
  SELECT TheTable.OBJECT_ID, TheTable.NAME, TheSchema.NAME,
    CASE WHEN identityColumns.Object_id IS NULL THEN 0 ELSE 1 END
  FROM sys.tables TheTable
    INNER JOIN sys.schemas TheSchema
      ON TheSchema.SCHEMA_ID = TheTable.schema_ID
    LEFT JOIN
       (SELECT DISTINCT Object_id
          FROM sys.columns
          WHERE is_identity = 1
       ) identityColumns
      ON TheTable.object_id = identityColumns.object_id;
/* We'll use a SQL 'set-based' form of the topological sort
First, find a list of "start nodes" which have no incoming edges
and insert them into a set S; at least one such node must exist 
in an acyclic graph*/--flag all the immediately safe tables to insert data in
UPDATE #tables
  SET TheOrder = 1
  FROM #tables parent 
  --do not reference any other table and aren't referenced by anything
    LEFT OUTER JOIN sys.foreign_Keys referenced
      ON referenced.referenced_Object_ID = parent.TheObject_ID
    LEFT OUTER JOIN sys.foreign_Keys referencing
      ON referencing.parent_Object_ID = parent.TheObject_ID
  WHERE referenced.parent_object_ID IS NULL
    AND referencing.parent_Object_ID IS NULL;
UPDATE #tables
  SET TheOrder = 2
  FROM #tables parent --do not reference any other table but might be referenced
    LEFT OUTER JOIN sys.foreign_Keys referencing
      ON referencing.parent_Object_ID = parent.TheObject_ID
        AND referencing.referenced_Object_ID <> parent.TheObject_ID
  WHERE referencing.parent_Object_ID IS NULL AND TheOrder = 0; 
  --i.e. it hasn't been ordered yet
SELECT @Rowcount = 100, @ii = 3;
--and then do tables successively as they become 'safe'
WHILE @Rowcount > 0
  BEGIN
  UPDATE #tables
    SET TheOrder = @ii
    WHERE #tables.TheObject_ID IN
    (
    SELECT parent.TheObject_ID
      FROM #tables parent
        INNER JOIN sys.foreign_Keys
          ON sys.foreign_Keys.parent_Object_ID = parent.TheObject_ID
        INNER JOIN #tables referenced
          ON sys.foreign_Keys.referenced_Object_ID = referenced.TheObject_ID
         AND sys.foreign_Keys.referenced_Object_ID <> parent.TheObject_ID
      WHERE parent.TheOrder = 0 --i.e. it hasn't been ordered yet
      GROUP BY parent.TheObject_ID
      HAVING --where all its referenced tables have been ordered
   SUM (CASE WHEN referenced.TheOrder = 0 
       THEN-20000 ELSE referenced.TheOrder END) > 0 
          );
  SET @Rowcount = @@Rowcount;  SET @ii = @ii + 1;
  IF @ii > 100
  BREAK;
  END;
SELECT TheObject_ID, TheSchema+'.'+TheName,  TheOrder
  FROM #tables
  ORDER BY TheOrder;
IF @ii > 100 --not a directed acyclic graph (DAG).
  RAISERROR('Cannot load in tables with mutual references in foreign keys',
  16, 1 );
IF EXISTS (SELECT * FROM #tables WHERE TheOrder = 0)
  RAISERROR('could not do the topological sort', 16, 1);
DROP table #tables

In the next level, we’ll deal with the problems of writing database CREATE and ALTER scripts that have to work appropriately whatever the state of the database. We will be describing many system views and Information_Schema Views that are needed to write DDL code in such a way that it works error-free without assumptions as to whether the objects that already exist, doesn’t get executed twice, or executed in the wrong circumstances.

This article is part of the parent stairway Stairway to Exploring Database Metadata

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

5 (5)

You rated this post out of 5. Change rating

Share

Join the discussion and add your comment

Share

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

5 (5)

You rated this post out of 5. Change rating

Related content

Stairway to Exploring Database Metadata

Idempotent DDL Scripts That Always Achieve The Same Result - Making Changes Only Once: Stairway to Exploring Database Metadata Level 6

Idempotence is the property of certain operations in mathematics and computer science, that can be applied multiple times without changing the result beyond the first time they are run. When you are making changes to a database to deploy a new version, you need to be sure that certain changes aren’t made twice, or in the wrong order.

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

5 (6)

You rated this post out of 5. Change rating

2023-10-04 (first published: )

19,041 reads

Stairway to SQL Server Indexes

Deeper into Nonclustered Indexes: Stairway to SQL Server Indexes Level 2

By this stage, you should be familiar with the basics of SQL Server indexes. We've discussed what an Index actually is, as well as some of the most common types you're likely to encounter. Now that we've seen some simple examples of how Indexes can be useful, we're going to delve deeper into nonclustered indexes, as we'll see how they can improve the performance of more complex queries.

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

5 (3)

You rated this post out of 5. Change rating

2022-11-16 (first published: )

37,557 reads

Stairway to T-SQL Beyond The Basics

Stairway to T-SQL: Beyond The Basics Level 3: Building a Correlated Subquery

This stairway level will expand on the subquery topic by discussing a type of subquery known as a correlated subquery, and explores what a correlated subquery is and how it is different from a normal subquery.

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

3.67 (3)

You rated this post out of 5. Change rating

2019-05-08 (first published: )

20,394 reads