Show Referential Integrity Relationship Constraints and Rules

The following commands show referential foreignkey/primary key constraints and rules that are defined in the database. The first command uses the INFORMATION_SCHEMA tables and thus may work in multiple DBMS vendors. It also provides an ALTER TABLE command that could recreate the relationship. The second command is based on SQL Server tables, but can be easily adpted to other DBMSs. Both use the STRING_AGG function to aggregate the column names for compound keys.

--Show referential constraints and rules.
SELECT RC.CONSTRAINT_NAME, MATCH_OPTION, UPDATE_RULE, DELETE_RULE
,KCU1.CONSTRAINT_NAME AS FK_CONSTRAINT
,KCU1.TABLE_SCHEMA AS FK_SCHEMA 
,KCU1.TABLE_NAME AS FK_TABLE
,FK_COLUMNS
,RC.UNIQUE_CONSTRAINT_NAME AS PK_CONSTRAINT
,KCU2.TABLE_SCHEMA AS PK_SCHEMA 
,KCU2.TABLE_NAME AS PK_TABLE
,PK_COLUMNS
,'ALTER TABLE '+KCU1.TABLE_SCHEMA+'.'+KCU1.TABLE_NAME+' ADD CONSTRAINT '+RC.CONSTRAINT_NAME
+' FOREIGN KEY ('+FK_COLUMNS+') REFERENCES '+KCU2.TABLE_SCHEMA+'.'+KCU2.TABLE_NAME+' ('+PK_COLUMNS
+') ON DELETE '+DELETE_RULE+' ON UPDATE '+UPDATE_RULE+';' AS COMMAND
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS RC 
JOIN (
	SELECT CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA, CONSTRAINT_NAME, TABLE_SCHEMA, TABLE_NAME
	,STRING_AGG(COLUMN_NAME, ', ') WITHIN GROUP (ORDER BY ORDINAL_POSITION) AS FK_COLUMNS
	FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
	GROUP BY CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA, CONSTRAINT_NAME, TABLE_SCHEMA, TABLE_NAME
) AS KCU1 
ON  KCU1.CONSTRAINT_CATALOG = RC.CONSTRAINT_CATALOG  
AND KCU1.CONSTRAINT_SCHEMA = RC.CONSTRAINT_SCHEMA 
AND KCU1.CONSTRAINT_NAME = RC.CONSTRAINT_NAME 
JOIN (
	SELECT CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA, CONSTRAINT_NAME, TABLE_SCHEMA, TABLE_NAME
	,STRING_AGG(COLUMN_NAME, ', ') WITHIN GROUP (ORDER BY ORDINAL_POSITION) AS PK_COLUMNS
	FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
	GROUP BY CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA, CONSTRAINT_NAME, TABLE_SCHEMA, TABLE_NAME
) AS KCU2
ON  KCU2.CONSTRAINT_CATALOG = RC.UNIQUE_CONSTRAINT_CATALOG  
AND KCU2.CONSTRAINT_SCHEMA = RC.UNIQUE_CONSTRAINT_SCHEMA 
AND KCU2.CONSTRAINT_NAME = RC.UNIQUE_CONSTRAINT_NAME 

 --Show PK/FK relationships Referential Integrity		
SELECT FK.name	ForeignKeyName
, OBJECT_NAME(FK.referenced_object_id)	ParentTable
, OBJECT_NAME(FK.parent_object_id)	ChildTable
, STRING_AGG(PC.Name,', ')	PrimaryColumns
, STRING_AGG(FC.Name,', ')	ForeignColumns
, is_disabled, is_not_trusted, delete_referential_action_desc, update_referential_action_desc
FROM SYS.FOREIGN_KEYS FK
JOIN SYS.FOREIGN_KEY_COLUMNS FKC
ON FKC.constraint_object_id = FK.object_id
JOIN SYS.COLUMNS PC
ON PC.Object_id = FKC.referenced_object_id
AND PC.Column_id = FKC.referenced_column_id
JOIN SYS.COLUMNS FC
ON FC.Object_id = FKC.parent_object_id
AND FC.Column_id = FKC.parent_column_id
--WHERE OBJECT_NAME(FK.referenced_object_id) IN ('LM04')
GROUP BY FK.Name, FK.parent_object_id, FK.referenced_object_id
, is_disabled, is_not_trusted, delete_referential_action_desc, update_referential_action_desc
ORDER BY FK.referenced_object_id, FK.Name

How to Profile Columns and their Data

Profiling the metadata and data in every column in every table is frequently done by a purchased package. Doing it yourself is a rather big undertaking. Most approaches will do a full table scan (FTS) for every column in the table. This could be unacceptably time-consuming. The following script will collect this data from the source tables 50 columns at a time in a single pass.

The data collected will show for every table and column the following information in a [Column_Profile] table:

• Schema Name
• Table Name
• Column_ID
• Column Name
• Extended Property
• [Column Name] (shown with square parentheses and embedded spaces)
• [Keep] (a subjective determination whether the column has any significant data; e.g. all NULL or only one value.)
• Percentage data with non-NULL values
• Data Type
• data_type_ext (includes size definition)
• Is the column Nullable? Yes/No
• Default_Value
• Is a Primary Key column "PK"
• Is a Foreign Key column "FK"
• Is a Unique Key column "UK"
• Check_Contraint
• Computed Column Definition
• Column Collation Map
• Number of Records
• Number of Not NULL Values
• Number of Distinct Values
• Minimum Value in this column over the table (this is a SQL_VARIANT column)
• Maximum Value in this column

DROP TABLE IF EXISTS [dbo].[Column_Profile_50];

--Create staging table to hold data profile.
CREATE TABLE [dbo].[Column_Profile_50]
( [SchemaName] varchar(50) NOT NULL	
, [TableName]  varchar(50) NOT NULL
, [Records] int NULL
, c1	varchar(70) --[ColumnName]			
, n1	int 		--[Not_NULL]
, d1	int 		--[Distinct_Values]
, a1	SQL_VARIANT	--[Min_Value]		
, b1	SQL_VARIANT	--[Max_Value]		
, c2 varchar(70), n2 int, d2 int, a2 SQL_VARIANT, b2 SQL_VARIANT 
, c3 varchar(70), n3 int, d3 int, a3 SQL_VARIANT, b3 SQL_VARIANT 
, c4 varchar(70), n4 int, d4 int, a4 SQL_VARIANT, b4 SQL_VARIANT 
, c5 varchar(70), n5 int, d5 int, a5 SQL_VARIANT, b5 SQL_VARIANT 
, c6 varchar(70), n6 int, d6 int, a6 SQL_VARIANT, b6 SQL_VARIANT 
, c7 varchar(70), n7 int, d7 int, a7 SQL_VARIANT, b7 SQL_VARIANT 
, c8 varchar(70), n8 int, d8 int, a8 SQL_VARIANT, b8 SQL_VARIANT 
, c9 varchar(70), n9 int, d9 int, a9 SQL_VARIANT, b9 SQL_VARIANT 
, c10 varchar(70), n10 int, d10 int, a10 SQL_VARIANT, b10 SQL_VARIANT 
, c11 varchar(70), n11 int, d11 int, a11 SQL_VARIANT, b11 SQL_VARIANT 
, c12 varchar(70), n12 int, d12 int, a12 SQL_VARIANT, b12 SQL_VARIANT 
, c13 varchar(70), n13 int, d13 int, a13 SQL_VARIANT, b13 SQL_VARIANT 
, c14 varchar(70), n14 int, d14 int, a14 SQL_VARIANT, b14 SQL_VARIANT 
, c15 varchar(70), n15 int, d15 int, a15 SQL_VARIANT, b15 SQL_VARIANT 
, c16 varchar(70), n16 int, d16 int, a16 SQL_VARIANT, b16 SQL_VARIANT 
, c17 varchar(70), n17 int, d17 int, a17 SQL_VARIANT, b17 SQL_VARIANT 
, c18 varchar(70), n18 int, d18 int, a18 SQL_VARIANT, b18 SQL_VARIANT 
, c19 varchar(70), n19 int, d19 int, a19 SQL_VARIANT, b19 SQL_VARIANT 
, c20 varchar(70), n20 int, d20 int, a20 SQL_VARIANT, b20 SQL_VARIANT 
, c21 varchar(70), n21 int, d21 int, a21 SQL_VARIANT, b21 SQL_VARIANT 
, c22 varchar(70), n22 int, d22 int, a22 SQL_VARIANT, b22 SQL_VARIANT 
, c23 varchar(70), n23 int, d23 int, a23 SQL_VARIANT, b23 SQL_VARIANT 
, c24 varchar(70), n24 int, d24 int, a24 SQL_VARIANT, b24 SQL_VARIANT 
, c25 varchar(70), n25 int, d25 int, a25 SQL_VARIANT, b25 SQL_VARIANT 
, c26 varchar(70), n26 int, d26 int, a26 SQL_VARIANT, b26 SQL_VARIANT 
, c27 varchar(70), n27 int, d27 int, a27 SQL_VARIANT, b27 SQL_VARIANT 
, c28 varchar(70), n28 int, d28 int, a28 SQL_VARIANT, b28 SQL_VARIANT 
, c29 varchar(70), n29 int, d29 int, a29 SQL_VARIANT, b29 SQL_VARIANT 
, c30 varchar(70), n30 int, d30 int, a30 SQL_VARIANT, b30 SQL_VARIANT 
, c31 varchar(70), n31 int, d31 int, a31 SQL_VARIANT, b31 SQL_VARIANT 
, c32 varchar(70), n32 int, d32 int, a32 SQL_VARIANT, b32 SQL_VARIANT 
, c33 varchar(70), n33 int, d33 int, a33 SQL_VARIANT, b33 SQL_VARIANT 
, c34 varchar(70), n34 int, d34 int, a34 SQL_VARIANT, b34 SQL_VARIANT 
, c35 varchar(70), n35 int, d35 int, a35 SQL_VARIANT, b35 SQL_VARIANT 
, c36 varchar(70), n36 int, d36 int, a36 SQL_VARIANT, b36 SQL_VARIANT 
, c37 varchar(70), n37 int, d37 int, a37 SQL_VARIANT, b37 SQL_VARIANT 
, c38 varchar(70), n38 int, d38 int, a38 SQL_VARIANT, b38 SQL_VARIANT 
, c39 varchar(70), n39 int, d39 int, a39 SQL_VARIANT, b39 SQL_VARIANT 
, c40 varchar(70), n40 int, d40 int, a40 SQL_VARIANT, b40 SQL_VARIANT 
, c41 varchar(70), n41 int, d41 int, a41 SQL_VARIANT, b41 SQL_VARIANT 
, c42 varchar(70), n42 int, d42 int, a42 SQL_VARIANT, b42 SQL_VARIANT 
, c43 varchar(70), n43 int, d43 int, a43 SQL_VARIANT, b43 SQL_VARIANT 
, c44 varchar(70), n44 int, d44 int, a44 SQL_VARIANT, b44 SQL_VARIANT 
, c45 varchar(70), n45 int, d45 int, a45 SQL_VARIANT, b45 SQL_VARIANT 
, c46 varchar(70), n46 int, d46 int, a46 SQL_VARIANT, b46 SQL_VARIANT 
, c47 varchar(70), n47 int, d47 int, a47 SQL_VARIANT, b47 SQL_VARIANT 
, c48 varchar(70), n48 int, d48 int, a48 SQL_VARIANT, b48 SQL_VARIANT 
, c49 varchar(70), n49 int, d49 int, a49 SQL_VARIANT, b49 SQL_VARIANT 
, c50 varchar(70), n50 int, d50 int, a50 SQL_VARIANT, b50 SQL_VARIANT 
)

--SELECT * FROM [dbo].[Column_Profile_50];
--TRUNCATE TABLE [dbo].[Column_Profile_50];

--Collect column profile data 50 columns at a time in Full Table Scans.		
DECLARE @table_schema varchar(100)		
DECLARE @table_name varchar(100)		
DECLARE @column_name varchar(100)		
DECLARE @current_table varchar(100)		
DECLARE @current_schema varchar(100)		
DECLARE @sql varchar(MAX)		
DECLARE @sql1 varchar(MAX)		
DECLARE @sql2 varchar(MAX)		
DECLARE @col int = 0

--Get columns for data profile.
DECLARE Cursor01 CURSOR LOCAL FAST_FORWARD FOR		
	SELECT A.TABLE_SCHEMA, A.TABLE_NAME, COLUMN_NAME 
	FROM INFORMATION_SCHEMA.COLUMNS a	
--	left join [DW].[Column_Profile_temp] x on a.TABLE_SCHEMA=x.SchemaName and a.TABLE_NAME=x.tableName and concat('[',a.COLUMN_NAME,']')=x.columnname  --this is to continue after failure
--	where x.TableName is null
	WHERE DATA_TYPE not in ('bit','text','ntext')	--Do not profile data in columns with these datatypes.
	AND CHARACTER_OCTET_LENGTH <> -1
--	AND a.TABLE_SCHEMA = 'Financials' --Change this filter if needed.
--	AND a.TABLE_NAME NOT LIKE '%TEST%'
	order by 1,2,3
OPEN Cursor01;		
FETCH NEXT FROM Cursor01 into @table_schema, @table_name, @column_name;		
WHILE @@FETCH_STATUS = 0		
BEGIN	
	--If new table then start building query
	IF @col=0
	BEGIN
		set @sql1 = 'insert into [dbo].Column_Profile_50 (SchemaName, TableName, Records'
		set @sql2 = replace(replace('select ''!'', ''^'', count(*)', '^',@table_name), '!',@table_schema)
	END
	--Add column to query
	set @col=@col+1
	set @sql1=concat(@sql1, replace(', c^, n^, d^, a^, b^', '^', @col))
	set @sql2=concat(@sql2, replace(', ''[?]'', count([?]), count(distinct [?]), min([?]), max([?])', '?',@column_name))
	--Get next column
	set @current_table  = @table_name
	set @current_schema = @table_schema
	FETCH NEXT FROM Cursor01 into @table_schema, @table_name, @column_name;	
	--If table name changed then finish query and execute
	IF @table_name<>@current_table OR @col=50 OR @@FETCH_STATUS <> 0
	BEGIN
		set @sql=concat(@sql1, ') ', @sql2, replace(replace(' from !.[^];', '^', @current_table), '!', @current_schema))
		PRINT @sql
		EXEC(@sql)
		set @col=0
	END
END		
CLOSE Cursor01;		
DEALLOCATE Cursor01;
GO

DROP TABLE IF EXISTS [dbo].[Column_Profile_temp]		

CREATE TABLE [dbo].[Column_Profile_temp](		
	[SchemaName] [varchar](50) NOT NULL,	
	[TableName] [varchar](50) NOT NULL,	
	[ColumnName] [varchar](70) NOT NULL,	
	[Records] [int] NULL,	
	[Not_NULL] [int] NULL,	
	[Distinct_Values] [int] NULL,	
	[Min_Value] SQL_VARIANT,	
	[Max_Value] SQL_VARIANT	
)

--Convert from 50 source columns per row to 1 row per source column.
INSERT INTO [dbo].[Column_Profile_temp]
SELECT [SchemaName], [TableName], c1, [Records], n1, d1, a1, b1 FROM [dbo].[Column_Profile_50] WHERE c1 IS NOT NULL UNION ALL
SELECT [SchemaName], [TableName], c2, [Records], n2, d2, a2, b2 FROM [dbo].[Column_Profile_50] WHERE c2 IS NOT NULL UNION ALL
SELECT [SchemaName], [TableName], c3, [Records], n3, d3, a3, b3 FROM [dbo].[Column_Profile_50] WHERE c3 IS NOT NULL UNION ALL
SELECT [SchemaName], [TableName], c4, [Records], n4, d4, a4, b4 FROM [dbo].[Column_Profile_50] WHERE c4 IS NOT NULL UNION ALL
SELECT [SchemaName], [TableName], c5, [Records], n5, d5, a5, b5 FROM [dbo].[Column_Profile_50] WHERE c5 IS NOT NULL UNION ALL
SELECT [SchemaName], [TableName], c6, [Records], n6, d6, a6, b6 FROM [dbo].[Column_Profile_50] WHERE c6 IS NOT NULL UNION ALL
SELECT [SchemaName], [TableName], c7, [Records], n7, d7, a7, b7 FROM [dbo].[Column_Profile_50] WHERE c7 IS NOT NULL UNION ALL
SELECT [SchemaName], [TableName], c8, [Records], n8, d8, a8, b8 FROM [dbo].[Column_Profile_50] WHERE c8 IS NOT NULL UNION ALL
SELECT [SchemaName], [TableName], c9, [Records], n9, d9, a9, b9 FROM [dbo].[Column_Profile_50] WHERE c9 IS NOT NULL UNION ALL
SELECT [SchemaName], [TableName], c10, [Records], n10, d10, a10, b10 FROM [dbo].[Column_Profile_50] WHERE c10 IS NOT NULL UNION ALL
SELECT [SchemaName], [TableName], c11, [Records], n11, d11, a11, b11 FROM [dbo].[Column_Profile_50] WHERE c11 IS NOT NULL UNION ALL
SELECT [SchemaName], [TableName], c12, [Records], n12, d12, a12, b12 FROM [dbo].[Column_Profile_50] WHERE c12 IS NOT NULL UNION ALL
SELECT [SchemaName], [TableName], c13, [Records], n13, d13, a13, b13 FROM [dbo].[Column_Profile_50] WHERE c13 IS NOT NULL UNION ALL
SELECT [SchemaName], [TableName], c14, [Records], n14, d14, a14, b14 FROM [dbo].[Column_Profile_50] WHERE c14 IS NOT NULL UNION ALL
SELECT [SchemaName], [TableName], c15, [Records], n15, d15, a15, b15 FROM [dbo].[Column_Profile_50] WHERE c15 IS NOT NULL UNION ALL
SELECT [SchemaName], [TableName], c16, [Records], n16, d16, a16, b16 FROM [dbo].[Column_Profile_50] WHERE c16 IS NOT NULL UNION ALL
SELECT [SchemaName], [TableName], c17, [Records], n17, d17, a17, b17 FROM [dbo].[Column_Profile_50] WHERE c17 IS NOT NULL UNION ALL
SELECT [SchemaName], [TableName], c18, [Records], n18, d18, a18, b18 FROM [dbo].[Column_Profile_50] WHERE c18 IS NOT NULL UNION ALL
SELECT [SchemaName], [TableName], c19, [Records], n19, d19, a19, b19 FROM [dbo].[Column_Profile_50] WHERE c19 IS NOT NULL UNION ALL
SELECT [SchemaName], [TableName], c20, [Records], n20, d20, a20, b20 FROM [dbo].[Column_Profile_50] WHERE c20 IS NOT NULL UNION ALL
SELECT [SchemaName], [TableName], c21, [Records], n21, d21, a21, b21 FROM [dbo].[Column_Profile_50] WHERE c21 IS NOT NULL UNION ALL
SELECT [SchemaName], [TableName], c22, [Records], n22, d22, a22, b22 FROM [dbo].[Column_Profile_50] WHERE c22 IS NOT NULL UNION ALL
SELECT [SchemaName], [TableName], c23, [Records], n23, d23, a23, b23 FROM [dbo].[Column_Profile_50] WHERE c23 IS NOT NULL UNION ALL
SELECT [SchemaName], [TableName], c24, [Records], n24, d24, a24, b24 FROM [dbo].[Column_Profile_50] WHERE c24 IS NOT NULL UNION ALL
SELECT [SchemaName], [TableName], c25, [Records], n25, d25, a25, b25 FROM [dbo].[Column_Profile_50] WHERE c25 IS NOT NULL UNION ALL
SELECT [SchemaName], [TableName], c26, [Records], n26, d26, a26, b26 FROM [dbo].[Column_Profile_50] WHERE c26 IS NOT NULL UNION ALL
SELECT [SchemaName], [TableName], c27, [Records], n27, d27, a27, b27 FROM [dbo].[Column_Profile_50] WHERE c27 IS NOT NULL UNION ALL
SELECT [SchemaName], [TableName], c28, [Records], n28, d28, a28, b28 FROM [dbo].[Column_Profile_50] WHERE c28 IS NOT NULL UNION ALL
SELECT [SchemaName], [TableName], c29, [Records], n29, d29, a29, b29 FROM [dbo].[Column_Profile_50] WHERE c29 IS NOT NULL UNION ALL
SELECT [SchemaName], [TableName], c30, [Records], n30, d30, a30, b30 FROM [dbo].[Column_Profile_50] WHERE c30 IS NOT NULL UNION ALL
SELECT [SchemaName], [TableName], c31, [Records], n31, d31, a31, b31 FROM [dbo].[Column_Profile_50] WHERE c31 IS NOT NULL UNION ALL
SELECT [SchemaName], [TableName], c32, [Records], n32, d32, a32, b32 FROM [dbo].[Column_Profile_50] WHERE c32 IS NOT NULL UNION ALL
SELECT [SchemaName], [TableName], c33, [Records], n33, d33, a33, b33 FROM [dbo].[Column_Profile_50] WHERE c33 IS NOT NULL UNION ALL
SELECT [SchemaName], [TableName], c34, [Records], n34, d34, a34, b34 FROM [dbo].[Column_Profile_50] WHERE c34 IS NOT NULL UNION ALL
SELECT [SchemaName], [TableName], c35, [Records], n35, d35, a35, b35 FROM [dbo].[Column_Profile_50] WHERE c35 IS NOT NULL UNION ALL
SELECT [SchemaName], [TableName], c36, [Records], n36, d36, a36, b36 FROM [dbo].[Column_Profile_50] WHERE c36 IS NOT NULL UNION ALL
SELECT [SchemaName], [TableName], c37, [Records], n37, d37, a37, b37 FROM [dbo].[Column_Profile_50] WHERE c37 IS NOT NULL UNION ALL
SELECT [SchemaName], [TableName], c38, [Records], n38, d38, a38, b38 FROM [dbo].[Column_Profile_50] WHERE c38 IS NOT NULL UNION ALL
SELECT [SchemaName], [TableName], c39, [Records], n39, d39, a39, b39 FROM [dbo].[Column_Profile_50] WHERE c39 IS NOT NULL UNION ALL
SELECT [SchemaName], [TableName], c40, [Records], n40, d40, a40, b40 FROM [dbo].[Column_Profile_50] WHERE c40 IS NOT NULL UNION ALL
SELECT [SchemaName], [TableName], c41, [Records], n41, d41, a41, b41 FROM [dbo].[Column_Profile_50] WHERE c41 IS NOT NULL UNION ALL
SELECT [SchemaName], [TableName], c42, [Records], n42, d42, a42, b42 FROM [dbo].[Column_Profile_50] WHERE c42 IS NOT NULL UNION ALL
SELECT [SchemaName], [TableName], c43, [Records], n43, d43, a43, b43 FROM [dbo].[Column_Profile_50] WHERE c43 IS NOT NULL UNION ALL
SELECT [SchemaName], [TableName], c44, [Records], n44, d44, a44, b44 FROM [dbo].[Column_Profile_50] WHERE c44 IS NOT NULL UNION ALL
SELECT [SchemaName], [TableName], c45, [Records], n45, d45, a45, b45 FROM [dbo].[Column_Profile_50] WHERE c45 IS NOT NULL UNION ALL
SELECT [SchemaName], [TableName], c46, [Records], n46, d46, a46, b46 FROM [dbo].[Column_Profile_50] WHERE c46 IS NOT NULL UNION ALL
SELECT [SchemaName], [TableName], c47, [Records], n47, d47, a47, b47 FROM [dbo].[Column_Profile_50] WHERE c47 IS NOT NULL UNION ALL
SELECT [SchemaName], [TableName], c48, [Records], n48, d48, a48, b48 FROM [dbo].[Column_Profile_50] WHERE c48 IS NOT NULL UNION ALL
SELECT [SchemaName], [TableName], c49, [Records], n49, d49, a49, b49 FROM [dbo].[Column_Profile_50] WHERE c49 IS NOT NULL UNION ALL
SELECT [SchemaName], [TableName], c50, [Records], n50, d50, a50, b50 FROM [dbo].[Column_Profile_50] WHERE c50 IS NOT NULL
ORDER BY 1,2,3;
GO

DROP TABLE IF EXISTS [dbo].[Column_Profile];

CREATE TABLE [dbo].Column_Profile(
	[Schema_Name] nvarchar(128) NULL,
	Table_Name sysname NOT NULL,
	Column_ID int NULL,
	Column_Name sysname NOT NULL,
	Extended_Property SQL_VARIANT NULL,
	[Column Name] varchar(130) NULL,
	[Keep] varchar(1) NULL,
	Pct_Valued int NULL,
	Data_Type sysname NULL,
	data_type_ext nvarchar(269) NULL,
	Nullable varchar(3) NULL,
	Default_Value nvarchar(max) NULL,
	Primary_Key varchar(2) NULL,
	Foreign_Key varchar(2) NULL,
	Unique_Key varchar(2) NULL,
	Check_contraint varchar(5) NULL,
	computed_column_definition nvarchar(max) NULL,
	ColumnCollation varchar(50),
	Records int NULL,
	Not_NULL int NULL,
	Distinct_Values int NULL,
	Min_Value SQL_VARIANT,
	Max_Value SQL_VARIANT
)
GO

 --Load column profile.
INSERT INTO [dbo].[Column_Profile]
SELECT schema_name(tbl.schema_id) AS Schema_Name
,tbl.NAME AS Table_Name
,col.Column_ID
,col.NAME AS Column_Name
,ep.value AS Extended_Property
,'['+TRIM(CAST(eq.value AS VARCHAR(50)))+' - '+TRIM(CAST(ep.value AS VARCHAR(75)))+']' AS [Column Name]

,case
 when ep.value is NULL THEN 'N'
 when P.[Records] < 2 then 'N'
 when P.[Distinct_Values] = 1 then 'N'
-- when 100.0*P.[Not_NULL]/CASE P.[Records] when 0 then 1 else P.[Records] end < 10 then 'N'
 when CAST(P.[Not_NULL] AS FLOAT)/CAST(P.[Records] AS FLOAT) < 0.005 then 'N'
 else 'Y'
 end AS Keep	--Determine if this column has any significant data (this is subjective).
 
,FLOOR(100.0*P.[Not_NULL]/CASE P.[Records] when 0 then 1 else P.[Records] end) AS Pct_Valued
,t.NAME AS Data_Type
,t.NAME 
+	CASE 
	WHEN t.is_user_defined = 0
	THEN isnull( '(' + 
		CASE 
		WHEN t.NAME IN ('binary', 'char', 'nchar', 'varchar', 'nvarchar', 'varbinary')
		THEN
			CASE col.max_length
			WHEN - 1
			THEN 'MAX'
			ELSE 
				CASE 
				WHEN t.NAME IN ('nchar', 'nvarchar')
				THEN cast(col.max_length / 2 AS VARCHAR(4))
				ELSE cast(col.max_length AS VARCHAR(4))
				END
			END
		WHEN t.NAME IN ('datetime2', 'datetimeoffset', 'time')
		THEN cast(col.scale AS VARCHAR(4))
		WHEN t.NAME IN ('decimal', 'numeric')
		THEN cast(col.precision AS VARCHAR(4)) + ', ' + cast(col.scale AS VARCHAR(4))
		END + ')', '')
	ELSE ':' + (
		SELECT c_t.NAME + isnull('(' + 
			CASE 
			WHEN c_t.NAME IN ('binary', 'char', 'nchar', 'varchar', 'nvarchar', 'varbinary')
			THEN 
				CASE c.max_length
				WHEN - 1
				THEN 'MAX'
				ELSE 
					CASE 
					WHEN t.NAME IN ('nchar', 'nvarchar')
					THEN cast(c.max_length / 2 AS VARCHAR(4))
					ELSE cast(c.max_length AS VARCHAR(4))
					END
				END
			WHEN c_t.NAME IN ('datetime2', 'datetimeoffset', 'time')
			THEN cast(c.scale AS VARCHAR(4))
			WHEN c_t.NAME IN ('decimal', 'numeric')
			THEN cast(c.precision AS VARCHAR(4)) + ', ' + cast(c.scale AS VARCHAR(4))
			END + ')', '')
		FROM sys.columns AS c
		INNER JOIN sys.types AS c_t ON c.system_type_id = c_t.user_type_id
		WHERE c.object_id = col.object_id AND c.column_id = col.column_id AND c.user_type_id = col.user_type_id
			)
	END AS data_type_ext
,CASE WHEN col.is_nullable = 0 THEN 'No' ELSE 'Yes' END AS Nullable
,CASE WHEN def.DEFINITION IS NOT NULL THEN def.DEFINITION ELSE '' END AS Default_Value
,CASE WHEN pk.column_id IS NOT NULL THEN 'PK' ELSE '' END AS Primary_Key
,CASE WHEN fk.parent_column_id IS NOT NULL THEN 'FK' ELSE '' END AS Foreign_Key
,CASE WHEN uk.column_id IS NOT NULL THEN 'UK' ELSE '' END AS Unique_Key
,CASE WHEN ch.check_const IS NOT NULL THEN ch.check_const ELSE '' END AS Check_contraint
,cc.DEFINITION AS computed_column_definition
,col.collation_name AS ColumnCollation
,P.[Records]
,P.[Not_NULL]
,P.[Distinct_Values]
,P.[Min_Value]
,P.[Max_Value]
FROM sys.tables AS tbl
JOIN sys.columns AS col ON tbl.object_id = col.object_id
JOIN sys.types AS t ON col.user_type_id = t.user_type_id
LEFT JOIN sys.default_constraints AS def ON def.object_id = col.default_object_id
LEFT JOIN (
	SELECT index_columns.object_id, index_columns.column_id
	FROM sys.index_columns
	INNER JOIN sys.indexes ON index_columns.object_id = indexes.object_id AND index_columns.index_id = indexes.index_id
	WHERE indexes.is_primary_key = 1
) AS pk ON col.object_id = pk.object_id AND col.column_id = pk.column_id
LEFT JOIN (
	SELECT fc.parent_column_id, fc.parent_object_id
	FROM sys.foreign_keys AS f
	INNER JOIN sys.foreign_key_columns AS fc ON f.object_id = fc.constraint_object_id
	GROUP BY fc.parent_column_id, fc.parent_object_id
) AS fk ON fk.parent_object_id = col.object_id AND fk.parent_column_id = col.column_id
LEFT JOIN (
	SELECT c.parent_column_id, c.parent_object_id, 'Check' check_const
	FROM sys.check_constraints AS c
	GROUP BY c.parent_column_id, c.parent_object_id
) AS ch ON col.column_id = ch.parent_column_id AND col.object_id = ch.parent_object_id
LEFT JOIN (
	SELECT index_columns.object_id, index_columns.column_id
	FROM sys.index_columns
	INNER JOIN sys.indexes ON indexes.index_id = index_columns.index_id AND indexes.object_id = index_columns.object_id
	WHERE indexes.is_unique_constraint = 1
	GROUP BY index_columns.object_id, index_columns.column_id
) AS uk ON col.column_id = uk.column_id AND col.object_id = uk.object_id
LEFT JOIN sys.extended_properties AS eq ON eq.major_id=tbl.object_id AND eq.minor_id=0 AND eq.class=1 AND eq.NAME = 'MS_Description'
LEFT JOIN sys.extended_properties AS ep 
	ON tbl.object_id = ep.major_id
	AND col.column_id = ep.minor_id
	AND ep.NAME = 'MS_Description'
	AND ep.class_desc = 'OBJECT_OR_COLUMN'
LEFT JOIN sys.computed_columns AS cc ON tbl.object_id = cc.object_id AND col.column_id = cc.column_id
LEFT JOIN [dbo].[Column_Profile_temp] P ON P.SchemaName = schema_name(tbl.schema_id) AND P.TableName = tbl.NAME AND P.ColumnName = CONCAT('[',col.NAME,']') --If you created this table		
--WHERE SCHEMA_NAME(tbl.schema_id) = 'Financials' --Filter if needed.
--AND tbl.TABLE_NAME NOT LIKE '%TEST%'
ORDER BY 1,2,3;
GO

SELECT * FROM [dbo].[Column_Profile] ORDER BY 1,2,3
GO

Table Profile

The following SQL Server code will create a "Table_Profile" that shows the following table metadata and static & dynamic volumetrics: 

• Table Owner 
• Schema Name 
• Table Name 
• Number of Columns 
• Number of Rows 
• Size (MB) 
• Has Primary Key 
• Has Identity Column 
• Has Unique Constraint 
• Has Cluster Index 
• Has an Index 
• Has a Foreign Key Constraint to a Parent Table 
• Is Referenced by other Child tables having a Foreign Key Constraint 
• Number of Referenced Parent Tables 
• Number of Referenced Child Tables 
• Has Check Constraint 
• Has Text Image (Blob) 
• Has Active Full Text Index 
• Has Row Guid Column 
• Has Timestamp 
• Has Trigger 
• Number of Index Seeks 
• Number of Index Scans 
• Number of Index Lookups 
• Number of Index Updates 
• Create Date 
• Modify Date 
• Last User Update 
• Last User Index Read 
• Extended Property Name 
• Extended Property Value (This is a table description you can assign using EXEC sys.sp_addextendedproperty)

 --Display table metadata and static & dynamic volumetrics.
SELECT 
  COALESCE((SELECT pr.name 		
            FROM sys.database_principals pr 		
            WHERE pr.principal_id = tbl.principal_id)		
           , SCHEMA_NAME(tbl.schema_id)) AS [Owner]		
, SCHEMA_NAME(tbl.schema_id) as [Schema]
, tbl.Name		
, tbl.max_column_id_used as [Columns]
--, (SELECT COUNT(*) 
--	FROM INFORMATION_SCHEMA.COLUMNS a
--	WHERE a.table_schema = SCHEMA_NAME(tbl.schema_id) AND a.table_name = tbl.Name ) AS [#Columns] 
, Coalesce( (Select sum (spart.rows) from sys.partitions spart 		
    Where spart.object_id = tbl.object_id and spart.index_id < 2), 0) AS [Rows]		
, "Size (MB)"
, OBJECTPROPERTY(tbl.object_id, 'TableHasPrimaryKey') AS [PK]
, OBJECTPROPERTY(tbl.object_id, 'TableHasIdentity')   AS [Identity Col]
, OBJECTPROPERTY(tbl.object_id, 'TableHasUniqueCnst') AS [Unique Cnt]
, OBJECTPROPERTY(tbl.object_id, 'TableHasClustIndex') AS [ClustIdx]
, OBJECTPROPERTY(tbl.object_id, 'TableHasIndex')      AS [Indexed]
, OBJECTPROPERTY(tbl.object_id, 'TableHasForeignKey') AS [FK Cnt]
, OBJECTPROPERTY(tbl.object_id, 'TableHasForeignRef') AS [FK Ref]
, Coalesce(A.Parents, 0) Parents
, Coalesce(C.Children, 0 ) Children
, OBJECTPROPERTY(tbl.object_id, 'TableHasCheckCnst')  AS [Check Cnt]
, OBJECTPROPERTY(tbl.object_id, 'TableHasTextImage')  AS [Blob]
, OBJECTPROPERTY(tbl.object_id, 'TableHasActiveFulltextIndex') AS [FT index]
, OBJECTPROPERTY(tbl.object_id, 'TableHasRowGuidCol') AS [ROWGUIDCOL]
, OBJECTPROPERTY(tbl.object_id, 'TableHasTimestamp')  AS [Timestamp]
, CASE 
  WHEN OBJECTPROPERTY(tbl.object_id, 'HasDeleteTrigger') = 1 THEN 1 
  WHEN OBJECTPROPERTY(tbl.object_id, 'HasInsertTrigger') = 1 THEN 1
  WHEN OBJECTPROPERTY(tbl.object_id, 'HasUpdateTrigger') = 1 THEN 1
  WHEN OBJECTPROPERTY(tbl.object_id, 'HasAfterTrigger')  = 1 THEN 1
  WHEN OBJECTPROPERTY(tbl.object_id, 'HasInsteadOfTrigger') = 1 THEN 1
  ELSE 0
  END AS [Trigger]
, L.index_seeks [Idx Seeks], L.index_scans [Idx Scan], L.index_lookups [Idx Lookups], L.index_updates [Idx Updates]		
, tbl.create_date, tbl.modify_date		
, L.last_user_update		
, L.last_user_index_read		
, P.name AS ExtendedPropertyName		
, CAST(P.value AS sql_variant) AS ExtendedPropertyValue
INTO [dbo].[Table_Profile]
FROM sys.tables AS tbl		
LEFT JOIN (SELECT object_id, SUM(reserved_page_count)*8.0/1024 AS "Size (MB)" FROM sys.dm_db_partition_stats GROUP BY object_id) D ON D.object_id=tbl.object_id
LEFT JOIN (SELECT PARENT_OBJECT_ID, COUNT(*) AS Parents FROM SYS.FOREIGN_KEY_COLUMNS GROUP BY PARENT_OBJECT_ID) A ON A.PARENT_OBJECT_ID=tbl.object_id
LEFT JOIN (SELECT REFERENCED_OBJECT_ID, COUNT(*) AS Children FROM SYS.FOREIGN_KEY_COLUMNS GROUP BY REFERENCED_OBJECT_ID) C ON C.REFERENCED_OBJECT_ID=tbl.object_id
LEFT JOIN (		
	SELECT OBJECT_ID	
	, SUM(user_seeks) index_seeks, SUM(user_scans) index_scans, SUM(user_lookups) index_lookups, MAX(user_updates) index_updates	
	, MAX(last_user_update) last_user_update	
	, MAX(last_user_index_read) last_user_index_read	
	FROM (	
		SELECT OBJECT_ID, user_seeks, user_scans, user_lookups, user_updates, last_user_update
		, (SELECT MAX(LastUpdateDate) FROM (VALUES (last_user_seek),(last_user_scan),(last_user_lookup)) AS last_user_read(LastUpdateDate)) last_user_index_read
		FROM sys.dm_db_index_usage_stats
		) L
	GROUP BY OBJECT_ID	
	) L	
ON L.object_id = tbl.object_id		
LEFT JOIN sys.extended_properties AS p ON p.major_id=tbl.object_id AND p.minor_id=0 AND p.class=1
--WHERE SCHEMA_NAME(tbl.schema_id) = 'Financials' --Filter if needed
ORDER BY 1,2,3