Get Table Metadata (2)

Applicability:

SQL Server 2000: Tested

SQL Server 2005: Tested

SQL Server 2008: Tested

SQL Server 2008R2: Tested

SQL Server 2012: Tested

SQL Server 2014: Tested

SQL Server 2016: Tested

SQL Server 2017: Not Tested

Credits:

Author:Phil Factor/ChillyDBA

Date: 5 Jun 2020

Description

These are 3 longer queries that generate lists of attributes that tables do, or do not possess, plus a large query to generate a list of tables in dependency order for use when performing inserts or deletes

Code

Get list of key attributes/functionality associated with all Tables:

SELECT @@SERVERNAME AS Server_Name,DB_NAME() AS Database_Name,OBJECT_SCHEMA_NAME(t.object_ID) AS Schema_Name,OBJECT_NAME(t.object_ID) AS Table_Name,OBJECT_SCHEMA_NAME(t.object_ID) +'.' + OBJECT_NAME(t.object_ID) AS Combined_Name--questions about indexes ,CASE WHEN OBJECTPROPERTYEX(object_id,'TableHasIndex') = 0THEN 'no' ELSE 'yes' END AS [Any index]--Table has an index of any type. ,CASE WHEN OBJECTPROPERTYEX(object_id,'TableHasClustIndex') = 0THEN 'no' ELSE 'yes' END AS [Clustered Index]--Table has a clustered index. ,CASE WHEN OBJECTPROPERTYEX(object_id,'TableHasPrimaryKey') = 0THEN 'no' ELSE 'yes' END AS [Primary Key]--Table has a primary key ,CASE WHEN OBJECTPROPERTYEX(object_id,'TableHasNonclustIndex') = 0THEN 'no' ELSE 'yes' END AS [nonCl Index]--Table has a nonclustered index. ,CASE WHEN OBJECTPROPERTYEX(object_id,'TableHasActiveFulltextIndex') = 0THEN 'no' ELSE 'yes' END AS [FT index]--Table has an active full-text index. --questions about constraints ,CASE WHEN OBJECTPROPERTYEX(object_id,'TableHasCheckCnst') = 0THEN 'no' ELSE 'yes' END AS [Check Cnst]--Table has a CHECK constraint. ,CASE WHEN OBJECTPROPERTYEX(object_id,'TableHasUniqueCnst') = 0THEN 'no' ELSE 'yes' END AS [Unique Cnst]--Table has a UNIQUE constraint. ,CASE WHEN OBJECTPROPERTYEX(object_id,'TableHasDefaultCnst') = 0THEN 'no' ELSE 'yes' END AS [Default Cnst]--Table has a DEFAULT constraint. ,CASE WHEN OBJECTPROPERTYEX(object_id,'TableHasForeignKey') = 0THEN 'no' ELSE 'yes' END AS [FK Cnst]--Table has a FOREIGN KEY constraint. ,CASE WHEN OBJECTPROPERTYEX(object_id,'TableHasForeignRef') = 0THEN 'no' ELSE 'yes' END AS [FK Ref]--Table is referenced by a FOREIGN KEY constraint. --questions about triggers ,CASE WHEN OBJECTPROPERTYEX(object_id,'TableHasInsertTrigger') = 0THEN 'no' ELSE 'yes' END AS [Insert Tgr]--Object has an INSERT trigger. ,CASE WHEN OBJECTPROPERTYEX(object_id,'TableHasUpdateTrigger') = 0THEN 'no' ELSE 'yes' END AS [Update Tgr]--Table has an UPDATE trigger. ,CASE WHEN OBJECTPROPERTYEX(object_id,'TableHasDeleteTrigger') = 0THEN 'no' ELSE 'yes' END AS [Delete Tgr]--Table has a DELETE trigger. --questions about types of columns ,CASE WHEN OBJECTPROPERTYEX(object_id,'TableHasIdentity') = 0THEN 'no' ELSE 'yes' END AS [Identity Col]--Table has an identity column. ,CASE WHEN OBJECTPROPERTYEX(object_id,'TableHasRowGuidCol') = 0THEN 'no' ELSE 'yes' END AS [ROWGUIDCOL]--has a ROWGUIDCOL for a uniqueidentifier col. ,CASE WHEN OBJECTPROPERTYEX(object_id,'TableHasTextImage') = 0THEN 'no' ELSE 'yes' END AS [Has Lob]--Table has a text, ntext, or image column. ,CASE WHEN OBJECTPROPERTYEX(object_id,'TableHasTimestamp') = 0THEN 'no' ELSE 'yes' END AS [Timestamp]--Table has a timestamp column. FROM sys.tables tORDER BY Table_Name

Get list of key attributes/functionality associated with all Tables:

-- list all problem aspects for tables SELECT @@SERVERNAME AS Server_Name,DB_NAME() AS Database_Name,OBJECT_SCHEMA_NAME(t.object_ID) AS Schema_Name,OBJECT_NAME(t.object_ID) AS Table_Name,OBJECT_SCHEMA_NAME(t.object_ID) +'.' + OBJECT_NAME(t.object_ID) AS Combined_Name,CASE WHEN OBJECTPROPERTY(OBJECT_ID, 'TableHasPrimaryKey') = 1 THEN 'No' ELSE 'Yes' END AS Has_No_Primary_Key,CASE WHEN OBJECTPROPERTY(OBJECT_ID, 'TableHasIndex') = 1 THEN 'No' ELSE 'Yes' END AS Has_No_Indexes,CASE WHEN OBJECTPROPERTY(OBJECT_ID, 'TableHasUniqueCnst') = 1 THEN 'No' ELSE 'Yes' END AS Has_No_Candidate_Key,CASE WHEN (SELECT 1 FROM sys.indexes i WHERE t.object_id = i.object_id AND is_disabled = 1) = 1 THEN 'Yes' ELSE 'No' END AS Has_Disabled_Indexes,CASE WHEN (SELECT 1 FROM sys.check_constraints c WHERE t.object_id = c.object_id AND is_disabled = 1) = 1 THEN 'Yes' ELSE 'No' END AS Has_Disabled_Constraints,CASE WHEN (SELECT 1 FROM sys.check_constraints c WHERE t.object_id = c.object_id AND is_not_trusted = 1) = 1 THEN 'Yes' ELSE 'No' END AS Has_Untrusted_Constraints,CASE WHEN (SELECT 1 FROM sys.foreign_keys f WHERE t.object_id = f.object_id AND is_disabled = 1) = 1 THEN 'Yes' ELSE 'No' END AS Has_Disabled_FK,CASE WHEN (SELECT 1 FROM sys.foreign_keys f WHERE t.object_id = f.object_id AND is_not_trusted = 1) = 1 THEN 'Yes' ELSE 'No' END AS Has_Untrusted_FK,CASE WHEN OBJECTPROPERTY(OBJECT_ID, 'TableHasForeignKey') = 0 AND OBJECTPROPERTYEX(OBJECT_ID, 'TableHasForeignRef') = 0 THEN 'Yes' ELSE 'No' END AS Has_No_FKs,CASE WHEN OBJECTPROPERTY(OBJECT_ID, 'IsIndexable') = 0 THEN 'Yes' ELSE 'No' END AS Is_Not_Indexable,CASE WHEN (SELECT 1 FROM sys.columns c WHERE t.object_id = c.object_idAND name COLLATE Latin1_General_CI_AI NOT LIKE '%[A-Z]%' COLLATE Latin1_General_CI_AI) = 1 THEN 'Yes' ELSE 'No' END AS Has_Unintelligible_Column_Names,CASE WHEN ( SELECT COUNT(*)FROM sys.foreign_keys keysINNER JOIN sys.foreign_key_columns TheColumnsON keys.Object_ID = constraint_object_idLEFT OUTER JOIN sys.index_columns icON ic.object_ID = TheColumns.parent_Object_IdAND ic.column_ID = TheColumns.parent_Column_IdAND TheColumns.constraint_column_ID = ic.key_ordinalWHERE ic.object_ID IS NULLAND keys.parent_object_id = t.object_id ) > 0 THEN 'Yes' ELSE 'No' END AS Has_FK_With_No_Index,CASE WHEN ( SELECT COUNT(*)FROM Sys.Index_Columns AS IcINNER JOIN sys.columns cON c.object_ID = Ic.object_ID AND c.column_ID = Ic.column_IDINNER JOIN sys.types tyON ty.system_type_id = c.system_type_idINNER JOIN sys.indexes iON i.object_ID = Ic.object_ID AND i.index_ID = Ic.index_IDWHERE t.name = 'uniqueidentifier'AND type_desc = 'CLUSTERED'AND OBJECTPROPERTY(Ic.OBJECT_ID, 'IsSystemTable') = 0AND ic.object_id = t.object_id) > 0 THEN 'Yes' ELSE 'No' END AS Has_CLustered_Index_on_GUID,CASE WHEN ( SELECT COUNT(*)FROM sys.columns cWHERE t.object_ID = c.object_IDAND name COLLATE Latin1_General_CI_AI LIKE'%[^_@$#A-Z0-9]%' COLLATE Latin1_General_CI_AI) > 0 THEN 'Yes' ELSE 'No' END AS Has_Column_Names_Requiring_Delimiters,CASE WHEN ( SELECT COUNT(*)FROM sys.triggers trINNER JOIN sys.sql_modules moON tr.object_ID = mo.object_IDWHERE tr.parent_ID = t.object_IDAND definition NOT LIKE '%set nocount on%') > 0 THEN 'Yes' ELSE 'No' END AS Has_Triggers_Without_NOCOUNT,CASE WHEN ( SELECT COUNT(*)FROM sys.Key_Constraints keysINNER JOIN sys.Index_columns TheColumnsON keys.Parent_Object_ID = TheColumns.Object_IDAND unique_index_ID = index_IDINNER JOIN sys.columns cON TheColumns.object_ID = c.object_IDAND TheColumns.column_ID = c.column_IDWHERE type = 'UQ' AND is_nullable = 1AND t.object_id = c.object_id) > 0 THEN 'Yes' ELSE 'No' END AS Has_UQ_With_Nullable_Column,CASE WHEN ( SELECT COUNT(*)FROM sys.sql_expression_dependenciesWHERE referenced_id = t.object_id) = 0 THEN 'Yes' ELSE 'No' END AS NOT_Referenced_By_Another_Object,CASE WHEN ( SELECT COUNT(*)FROM sys.triggersWHERE is_disabled = 1AND parent_ID =t.object_id) > 0 THEN 'Yes' ELSE 'No' END AS Has_Disabled_TriggerFROM sys.tables t/* --included the source queries to be able to deal with each attribute individually --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_IDWHERE 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 smellFROM sys.objects s /* it has no extended properties */LEFT OUTER JOIN sys.extended_properties epON s.object_ID = ep.major_ID AND minor_ID = 0WHERE 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 smellFROM 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 smellFROM 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 smellFROM 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 smellFROM 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 smellFROM 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 smellFROM 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 smellFROM 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 smellFROM 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 smellFROM sys.tables /* found a simpler way! */WHERE OBJECTPROPERTYEX(OBJECT_ID, 'TableHasForeignKey') = 0AND 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 smellFROM 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 smellFROM sys.foreign_keys keysINNER JOIN sys.foreign_key_columns TheColumnsON keys.Object_ID = constraint_object_idLEFT OUTER JOIN sys.index_columns icON ic.object_ID = TheColumns.parent_Object_IdAND ic.column_ID = TheColumns.parent_Column_IdAND TheColumns.constraint_column_ID = ic.key_ordinalWHERE 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 IcINNER JOIN sys.columns cON c.object_ID = Ic.object_ID AND c.column_ID = Ic.column_IDINNER JOIN sys.types tON t.system_type_id = c.system_type_idINNER JOIN sys.indexes iON i.object_ID = Ic.object_ID AND i.index_ID = Ic.index_IDWHERE 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 smellFROM 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 smellFROM sys.tables ta /* see whether the table has any index */INNER JOIN sys.triggers tr ON tr.parent_ID = ta.object_IDINNER JOIN sys.sql_modules moON tr.object_ID = mo.object_IDWHERE 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 smellFROM sys.tables /* found a simpler way! */LEFT OUTER JOIN sys.sql_expression_dependenciesON referenced_id = sys.tables.object_idWHERE 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 smellFROM sys.triggersWHERE 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 smellFROM sys.tablesWHERE 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 smellFROM sys.Key_Constraints keysINNER JOIN sys.Index_columns TheColumnsON keys.Parent_Object_ID = TheColumns.Object_IDAND unique_index_ID = index_IDINNER JOIN sys.columns cON TheColumns.object_ID = c.object_IDAND TheColumns.column_ID = c.column_IDWHERE type = 'UQ' AND is_nullable = 1;*/

Get a list of Tables in dependency order (optional parameter for list of tables in scope):

--- NOTE -- Add or remove rows to the #RequiredTables table if you are only interested in the relative order for a few tables -- Change the @ActionType from INSERT to DELETE and the order of results will be reversed to accommodate SET NOCOUNT ON;IF OBJECT_ID('tempdb..#TableDependencyOrder') IS NOT NULLDROP TABLE #TableDependencyOrderGOIF OBJECT_ID('tempdb..#RequiredTables') IS NOT NULLDROP TABLE #RequiredTablesGODECLARE @Rowcount INT,@ii INT,@Action VARCHAR(20)SELECT @Action = 'INSERT'--SELECT @Action = 'DELETE' CREATE TABLE #TableDependencyOrder(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 CREATE TABLE #RequiredTables(TheSchema SYSNAME --the schema where it lives ,TheName SYSNAME, --the name of the table )INSERT #RequiredTables (TheSchema, TheName)VALUES('dbo','My_First_Table'),('dbo','My_Secont_Table')--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 #TableDependencyOrder(TheObject_ID,TheName,TheSchema,HasIdentityColumn)SELECT t.OBJECT_ID, t.NAME, s.NAME,CASE WHEN ic.Object_id IS NULL THEN 0 ELSE 1 ENDFROM sys.tables tINNER JOIN sys.schemas sON s.SCHEMA_ID = t.schema_IDLEFT JOIN(SELECT DISTINCT Object_idFROM sys.columnsWHERE is_identity = 1) AS icON t.object_id = ic.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 #TableDependencyOrderSET TheOrder = 1FROM #TableDependencyOrder parent--do not reference any other table and aren't referenced by anything LEFT OUTER JOIN sys.foreign_Keys referencedON referenced.referenced_Object_ID = parent.TheObject_IDLEFT OUTER JOIN sys.foreign_Keys referencingON referencing.parent_Object_ID = parent.TheObject_IDWHERE referenced.parent_object_ID IS NULLAND referencing.parent_Object_ID IS NULLUPDATE #TableDependencyOrderSET TheOrder = 2FROM #TableDependencyOrder parent --do not reference any other table but might be referenced LEFT OUTER JOIN sys.foreign_Keys referencingON referencing.parent_Object_ID = parent.TheObject_IDAND referencing.referenced_Object_ID <> parent.TheObject_IDWHERE 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) AND (@ii <= 100)BEGINUPDATE #TableDependencyOrderSET TheOrder = @iiWHERE #TableDependencyOrder.TheObject_ID IN(SELECT parent.TheObject_IDFROM #TableDependencyOrder parentINNER JOIN sys.foreign_KeysON sys.foreign_Keys.parent_Object_ID = parent.TheObject_IDINNER JOIN #TableDependencyOrder referencedON sys.foreign_Keys.referenced_Object_ID = referenced.TheObject_IDAND sys.foreign_Keys.referenced_Object_ID <> parent.TheObject_IDWHERE parent.TheOrder = 0 --i.e. it hasn't been ordered yet GROUP BY parent.TheObject_IDHAVING --where all its referenced tables have been ordered SUM (CASE WHEN referenced.TheOrder = 0 THEN-20000 ELSE referenced.TheOrder END) > 0)SET @Rowcount = @@ROWCOUNTSET @ii = @ii + 1ENDIF (SELECT COUNT(*) FROM #RequiredTables ) = 0BEGINSELECT @@SERVERNAME AS Server_Name,DB_NAME() AS Database_Name,OBJECT_SCHEMA_NAME(TheObject_ID) AS Schema_Name,OBJECT_NAME(TheObject_ID) AS Table_Name,@Action AS ActionType,OBJECT_SCHEMA_NAME(TheObject_ID) +'.' + OBJECT_NAME(TheObject_ID) AS Combined_Name-- ,TheOrder AS Action_Dependency_Order FROM #TableDependencyOrderORDER BY CASE WHEN @Action = 'INSERT' THEN (TheOrder) ELSE (TheOrder * -1) ENDENDELSEBEGINSELECT @@SERVERNAME AS Server_Name,DB_NAME() AS Database_Name,OBJECT_SCHEMA_NAME(do.TheObject_ID) AS Schema_Name,OBJECT_NAME(do.TheObject_ID) AS Table_Name,@Action AS ActionType,OBJECT_SCHEMA_NAME(do.TheObject_ID) +'.' + OBJECT_NAME(do.TheObject_ID) AS Combined_Name-- ,TheOrder AS Action_Dependency_Order FROM #TableDependencyOrder doINNER JOIN #RequiredTables rtON do.TheSchema = rt.TheSchemaAND do.TheName = rt.TheNameORDER BY CASE WHEN @Action = 'INSERT' THEN (TheOrder) ELSE (TheOrder * -1) ENDENDIF @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 #TableDependencyOrder WHERE TheOrder = 0)RAISERROR('could not do the topological sort', 16, 1);DROP table #TableDependencyOrder