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 intersted 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