Cool Tips‎ > ‎Tables and Triggers‎ > ‎

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') = 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 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_id AND 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 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 AND 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 Ic INNER JOIN sys.columns c ON c.object_ID = Ic.object_ID AND c.column_ID = Ic.column_ID INNER JOIN sys.types ty ON ty.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 AND 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 c WHERE t.object_ID = c.object_ID AND 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 tr INNER JOIN sys.sql_modules mo ON tr.object_ID = mo.object_ID WHERE tr.parent_ID = t.object_ID AND 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 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 AND 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_dependencies WHERE referenced_id = t.object_id) = 0 THEN 'Yes' ELSE 'No' END AS NOT_Referenced_By_Another_Object ,CASE WHEN ( SELECT COUNT(*) FROM sys.triggers WHERE is_disabled = 1 AND parent_ID =t.object_id) > 0 THEN 'Yes' ELSE 'No' END AS Has_Disabled_Trigger FROM 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_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; */





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 NULL DROP TABLE #TableDependencyOrder GO IF OBJECT_ID('tempdb..#RequiredTables') IS NOT NULL DROP TABLE #RequiredTables GO DECLARE @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 END FROM sys.tables t INNER JOIN sys.schemas s ON s.SCHEMA_ID = t.schema_ID LEFT JOIN ( SELECT DISTINCT Object_id FROM sys.columns WHERE is_identity = 1 ) AS ic ON 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 #TableDependencyOrder SET TheOrder = 1 FROM #TableDependencyOrder 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 #TableDependencyOrder SET TheOrder = 2 FROM #TableDependencyOrder 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) AND (@ii <= 100) BEGIN UPDATE #TableDependencyOrder SET TheOrder = @ii WHERE #TableDependencyOrder.TheObject_ID IN ( SELECT parent.TheObject_ID FROM #TableDependencyOrder parent INNER JOIN sys.foreign_Keys ON sys.foreign_Keys.parent_Object_ID = parent.TheObject_ID INNER JOIN #TableDependencyOrder 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 END IF (SELECT COUNT(*) FROM #RequiredTables ) = 0 BEGIN SELECT @@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 #TableDependencyOrder ORDER BY CASE WHEN @Action = 'INSERT' THEN (TheOrder) ELSE (TheOrder * -1) END END ELSE BEGIN SELECT @@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 do INNER JOIN #RequiredTables rt ON do.TheSchema = rt.TheSchema AND do.TheName = rt.TheName ORDER BY CASE WHEN @Action = 'INSERT' THEN (TheOrder) ELSE (TheOrder * -1) END END 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 #TableDependencyOrder WHERE TheOrder = 0) RAISERROR('could not do the topological sort', 16, 1); DROP table #TableDependencyOrder