MS SQL Tools

mssql-tools.sql

-- List all tables containing column named [columnname]
SELECT      c.name  AS 'ColumnName'
            ,t.name AS 'TableName'
FROM        sys.columns c
JOIN        sys.tables  t   ON c.object_id = t.object_id
WHERE       c.name LIKE '%columnname%'
ORDER BY    TableName
            ,ColumnName;
            
            
-- List all tables without any tables referencing them and no
-- foreign key referencing other tables
;
WITH AllTables AS (
	SELECT 
		SCHEMA_NAME(T.schema_id) AS SchemaName,
		T.[name] AS TableName
	FROM sys.tables T
	),
ForeignKeys AS (
	SELECT 
		FK.object_id,
		SCHEMA_NAME(FK.schema_id) AS SchemaName,
		FK.[Name] AS ForeignKeyName,
		OBJECT_NAME(FK.parent_object_id) AS PrimaryTable,
		OBJECT_NAME(FK.referenced_object_id) AS ForeignTable
	FROM sys.foreign_keys FK
),
ReferenceList AS (
	SELECT 
		A.SchemaName, 
		A.TableName,
		IIF(A.TableName = FK.PrimaryTable, 1, 0) AS IsParent,
		IIF(A.TableName = FK.ForeignTable, 1, 0) AS IsChild
		FROM AllTables A
		LEFT JOIN ForeignKeys FK ON FK.SchemaName = FK.SchemaName AND (A.TableName = FK.PrimaryTable OR A.TableName = FK.ForeignTable)
)

SELECT R.SchemaName, R.TableName FROM ReferenceList R
WHERE R.IsParent = 0 AND R.IsChild = 0
ORDER BY R.SchemaName, R.TableName