SQL Script for Table and Column Properties in a SQL Server Database

By: Hadi Fadlallah | Updated: 2023-09-29 | Comments (6) | Related: > Database Design

Problem

Over the last decade, we have experienced an unprecedented surge in data, primarily driven by the exponential expansion of social media platforms and the Internet of Things. This data explosion offered tremendous potential for organizations and introduced substantial complexities. Given the immense volume, speed, and diversity of data being generated, creating profiles for each data source has become imperative to enable organizations to broadly comprehend the attributes, quality, and structure of data originating from these diverse sources.

Solution

Data profiling means examining and understanding data. Profiling reveals data characteristics, structure, content, and quality. It involves assessing data values, identifying patterns, and gaining insights into distribution, relationships, and statistical properties.

This step-by-step technical article will explore how to perform data profiling on a database using SQL code. We will use a combination of SQL queries to gather various metrics and statistics about the data, such as column data types, null values, data length, and more. Ultimately, all SQL code portions are combined within a comprehensive script I used for years to examine SQL Server databases.

Each set of code needs to be run and the very last set of code returns the overall results for the data collected. Also, the DECLARE sections were repeated below to make it easier to copy the code and run as is. There is also a link at the very end of the article that has the entire code.

For this example, we will be collecting data from the AdventureWorks database, but this can be applied against any database.

Setup and Data Profiling Table Creation

The first part of our SQL code sets up the environment by creating a temporary table named ##Statistics. This table will store the results of our data profiling analysis for each column in the database tables. The columns in ##Statistics represent the tables and columns metadata besides various metrics we will calculate, such as column data type, null values count, maximum length, etc.

--MSSQLTips.com
IF OBJECT_ID('tempdb..##Statistics') IS NOT NULL DROP TABLE  ##Statistics
IF OBJECT_ID('tempdb..#tblTemp') IS NOT NULL DROP TABLE  #tblTemp
 
-- Create data profiling table
CREATE TABLE ##Statistics(
[table_schema] varchar(50),
[table_name] varchar(50),
[column_name] varchar(50),
[is_nullable] bit,
[data_type] varchar(20),
[default_value] varchar(50),
[max_length] int,
[max_length_reality] int,
[null_values] bigint,
[empty_strings_count] bigint,
[values_count] bigint,
[min_value] numeric(18,6),
[max_value] numeric(18,6),
[avg_value] numeric(18,6),
[stdev_value] numeric(18,6),
[median_value] numeric(18,6),
[date_before_1902] bigint,
[date_after_current] bigint,
[date_type_warning] bit,
[time_type_warning] bit,
[length_warning] bit,
[column_is_primary] bit,
[column_has_constraints] bit,
[column_is_foreign] bit,
[column_is_indexed] bit,
[column_is_identity] bit,
[column_is_partitioned] bit,
[table_row_count] bigint,
[table_total_space_KB] bigint,
[table_index_space_KB] bigint,
[table_used_space_KB] bigint,
[table_unused_space_KB] bigint,
[table_space_warning] bit,
[table_indexes_count] int,
[table_has_primary] bit,
[table_has_clustered_index] bit,
[table_partitions_count] int
)

Inserting Columns Information

Next, we use an INSERT INTO statement to populate the ##Statistics table with column information from the INFORMATION_SCHEMA.COLUMNS view. This view contains metadata about all columns in the database tables, such as column names, data types, and maximum lengths.

--MSSQLTips.com
 
Insert into ##Statistics(
[table_schema],[table_name],[column_name],[is_nullable],[data_type],[default_value],[max_length])
Select c.[table_schema],c.[table_name],c.[column_name], case c.[is_nullable] when 'NO' then 0 else 1 end,
c.data_type, c.column_default, 
case when c.character_maximum_length is not null then c.character_maximum_length
     when c.numeric_precision is not null then c.numeric_precision
    else null end 
from information_schema.columns c WHERE 
    not exists (select * from information_schema.views v 
   where v.table_name = c.table_name and v.table_schema = c.table_schema)

Calculating Columns Metrics

We use a cursor to loop through all tables in the database and calculate specific metrics for each table's columns. We create a temp table that stores the columns' metrics before updating them within the final result table we created at the beginning of our code.

Declare @strTablename nvarchar(100)
Declare @strSchemaname nvarchar(100)
Declare @strQuery nvarchar(max)
Declare @strSecondaryQuery nvarchar(max)
Declare @strUnpivot nvarchar(max)
 
Create table #tblTemp([Schema] varchar(50), [Table] varchar(50), [Column] varchar(50), [FuncValue] numeric(18,6))
 
Declare csr Cursor for select table_schema, table_name from INFORMATION_SCHEMA.tables
 
Open csr
 
Fetch next from csr into @strSchemaname, @strTablename
 
While @@fetch_status  = 0
Begin
 
-- The code is mentioned in the following subsections
 
Fetch next from csr into @strSchemaname, @strTablename
 
End
 
close csr
deallocate csr

Calculate Non-null Values for Nullable Columns

The metric calculated is the total number of values in the column. We created a dynamic SQL query that calculates those values and applies pivoting to store them within the temp table created.

--MSSQLTips.com

Declare @strTablename nvarchar(100)
Declare @strSchemaname nvarchar(100)
Declare @strQuery nvarchar(max)
Declare @strSecondaryQuery nvarchar(max)
Declare @strUnpivot nvarchar(max)
 
Set @strQuery = ''
Set @strSecondaryQuery = 'Select '
Set @strUnpivot = ' Unpivot ([FuncValue] for [Column] IN ('
 
Truncate table #tblTemp
 
If exists(select * from INFORMATION_SCHEMA.columns where table_name = @strTablename and table_schema = @strSchemaname and IS_NULLABLE = 'Yes' )
Begin
 
Select @strQuery = ISNULL(@strQuery,'') + 'Count([' + Column_name + ']) as [' + column_name + '],' from INFORMATION_SCHEMA.columns where table_name = @strTablename and table_schema = @strSchemaname and IS_NULLABLE = 'Yes' 
 
Select @strUnpivot = ISNULL(@strUnpivot,'') + '[' + column_name + '] ,' from INFORMATION_SCHEMA.columns where table_name = @strTablename and table_schema = @strSchemaname and IS_NULLABLE = 'Yes' 
 
Set @strQuery = 'Select ''' + @strSchemaname + ''' AS [Schema],''' + @strTablename + ''' as [Table], [Column], [FuncValue] From (Select ' + Substring(@strQuery,1,LEN(@strQuery) -1) + ' From [' + @strSchemaname + '].[' + @strTablename + ']) As p ' + SUBSTRING(@strUnpivot,1,Len(@strUnpivot) - 1) + ')) As unpvt '
 
Insert Into #tblTemp Exec (@strQuery)
 
Update T1
Set T1.[Values_Count] = T2.FuncValue
FROM ##Statistics T1 inner join #tblTemp T2 ON T1.table_schema = T2.[Schema] and T1.table_name = T2.[Table] and T1.column_name = T2.[Column]
 
end

Calculate the Maximum Length of Values

Similar to the previous step, we calculate each column's maximum length of values. We consider the actual data length in the column instead of the maximum length specified in the schema.

--MSSQLTips.com
 
Declare @strTablename nvarchar(100)
Declare @strSchemaname nvarchar(100)
Declare @strQuery nvarchar(max)
Declare @strSecondaryQuery nvarchar(max)
Declare @strUnpivot nvarchar(max) 
 
Set @strQuery = ''
Set @strSecondaryQuery = 'Select '
Set @strUnpivot = ' Unpivot ([FuncValue] for [Column] IN ('
 
Truncate table #tblTemp
 
If exists(select * from INFORMATION_SCHEMA.columns where table_name = @strTablename and table_schema = @strSchemaname )
Begin
 
Select @strQuery = ISNULL(@strQuery,'') + 'MAX(LEN([' + Column_name + '])) as [' + column_name + '],' from INFORMATION_SCHEMA.columns where table_name = @strTablename and table_schema = @strSchemaname 
 
Select @strUnpivot = ISNULL(@strUnpivot,'') + '[' + column_name + '] ,' from INFORMATION_SCHEMA.columns where table_name = @strTablename and table_schema = @strSchemaname 
 
Set @strQuery = 'Select ''' + @strSchemaname + ''' AS [Schema],''' + @strTablename + ''' as [Table], [Column], [FuncValue] From (Select ' + Substring(@strQuery,1,LEN(@strQuery) -1) + ' From [' + @strSchemaname + '].[' + @strTablename + ']) As p ' + SUBSTRING(@strUnpivot,1,Len(@strUnpivot) - 1) + ')) As unpvt '
 
Insert Into #tblTemp Exec (@strQuery)
 
Update T1
Set T1.[max_length_reality] = T2.FuncValue
FROM ##Statistics T1 inner join #tblTemp T2 ON T1.table_schema = T2.[Schema] and T1.table_name = T2.[Table] and T1.column_name = T2.[Column]
 
end

Calculate the Occurrence of Empty Strings

In this step, we calculate the count of empty strings in columns with character data types. We differentiate between null values and empty strings.

--MSSQLTips.com

Declare @strTablename nvarchar(100)
Declare @strSchemaname nvarchar(100)
Declare @strQuery nvarchar(max)
Declare @strSecondaryQuery nvarchar(max)
Declare @strUnpivot nvarchar(max)
 
Set @strQuery = ''
Set @strSecondaryQuery = 'Select '
Set @strUnpivot = ' Unpivot ([FuncValue] for [Column] IN ('
 
Truncate Table #tblTemp
 
If exists(select * from INFORMATION_SCHEMA.columns where table_name = @strTablename and table_schema = @strSchemaname and DATA_TYPE like '%char%' )
Begin
 
Select @strQuery = ISNULL(@strQuery,'') + 'sum(case when [' + column_name + '] is null then 0 when ltrim(rtrim([' + column_name + '])) = '''' then 1 else 0 end) as [' + column_name + '],' from INFORMATION_SCHEMA.columns where table_name = @strTablename and table_schema = @strSchemaname and DATA_TYPE like '%char%' 
 
Select @strUnpivot = ISNULL(@strUnpivot,'') + '[' + column_name + '] ,' from INFORMATION_SCHEMA.columns where table_name = @strTablename and table_schema = @strSchemaname and DATA_TYPE like '%char%' 
 
Set @strQuery = 'Select ''' + @strSchemaname + ''' AS [Schema],''' + @strTablename + ''' as [Table], [Column], [FuncValue] From (Select ' + Substring(@strQuery,1,LEN(@strQuery) -1) + ' From [' + @strSchemaname + '].[' + @strTablename + ']) As p ' + SUBSTRING(@strUnpivot,1,Len(@strUnpivot) - 1) + ')) As unpvt '
 
Insert Into #tblTemp Exec (@strQuery)
 
Update T1
Set T1.[empty_strings_count] = T2.FuncValue
FROM ##Statistics T1 inner join #tblTemp T2 ON T1.table_schema = T2.[Schema] and T1.table_name = T2.[Table] and T1.column_name = T2.[Column]
 
end

Calculate the Occurrence of Date Values Before 1902

This part of the code calculates the count of date values that fall before January 1, 1902, in columns with date data types.

--MSSQLTips.com

Declare @strTablename nvarchar(100)
Declare @strSchemaname nvarchar(100)
Declare @strQuery nvarchar(max)
Declare @strSecondaryQuery nvarchar(max)
Declare @strUnpivot nvarchar(max)
 
Set @strQuery = ''
Set @strSecondaryQuery = 'Select '
Set @strUnpivot = ' Unpivot ([FuncValue] for [Column] IN ('
 
Truncate Table #tblTemp
 
If exists(select * from INFORMATION_SCHEMA.columns where table_name = @strTablename and table_schema = @strSchemaname and DATA_TYPE like '%date%' )
Begin
 
Select @strQuery = ISNULL(@strQuery,'') + 'sum(case when [' + column_name + '] is null then 0 when [' + column_name + '] <= ''19020101'' then 1 else 0 end) as [' + column_name + '],' from INFORMATION_SCHEMA.columns where table_name = @strTablename and table_schema = @strSchemaname and DATA_TYPE like '%date%' 
 
Select @strUnpivot = ISNULL(@strUnpivot,'') + '[' + column_name + '] ,' from INFORMATION_SCHEMA.columns where table_name = @strTablename and table_schema = @strSchemaname and DATA_TYPE like '%date%' 
 
Set @strQuery = 'Select ''' + @strSchemaname + ''' AS [Schema],''' + @strTablename + ''' as [Table], [Column], [FuncValue] From (Select ' + Substring(@strQuery,1,LEN(@strQuery) -1) + ' From [' + @strSchemaname + '].[' + @strTablename + ']) As p ' + SUBSTRING(@strUnpivot,1,Len(@strUnpivot) - 1) + ')) As unpvt '
 
Insert Into #tblTemp Exec (@strQuery)
 
Update T1
Set T1.[date_before_1902] = T2.FuncValue
FROM ##Statistics T1 inner join #tblTemp T2 ON T1.table_schema = T2.[Schema] and T1.table_name = T2.[Table] and T1.column_name = T2.[Column]
 
end

Calculate the Occurrence of Date Values after the Current Date

Similarly, we calculate the count of date values greater than the current date in columns with date data types.

--MSSQLTips.com

Declare @strTablename nvarchar(100)
Declare @strSchemaname nvarchar(100)
Declare @strQuery nvarchar(max)
Declare @strSecondaryQuery nvarchar(max)
Declare @strUnpivot nvarchar(max)
 
Set @strQuery = ''
Set @strSecondaryQuery = 'Select '
Set @strUnpivot = ' Unpivot ([FuncValue] for [Column] IN ('
 
Truncate Table #tblTemp
 
If exists(select * from INFORMATION_SCHEMA.columns where table_name = @strTablename and table_schema = @strSchemaname and DATA_TYPE like '%date%' )
Begin
 
Select @strQuery = ISNULL(@strQuery,'') + 'sum(case when [' + column_name + '] is null then 0 when [' + column_name + '] > GETDATE() then 1 else 0 end) as [' + column_name + '],' from INFORMATION_SCHEMA.columns where table_name = @strTablename and table_schema = @strSchemaname and DATA_TYPE like '%date%' 
 
Select @strUnpivot = ISNULL(@strUnpivot,'') + '[' + column_name + '] ,' from INFORMATION_SCHEMA.columns where table_name = @strTablename and table_schema = @strSchemaname and DATA_TYPE like '%date%' 
 
Set @strQuery = 'Select ''' + @strSchemaname + ''' AS [Schema],''' + @strTablename + ''' as [Table], [Column], [FuncValue] From (Select ' + Substring(@strQuery,1,LEN(@strQuery) -1) + ' From [' + @strSchemaname + '].[' + @strTablename + ']) As p ' + SUBSTRING(@strUnpivot,1,Len(@strUnpivot) - 1) + ')) As unpvt '
 
Insert Into #tblTemp Exec (@strQuery)
 
Update T1
Set T1.[date_after_current] = T2.FuncValue
FROM ##Statistics T1 inner join #tblTemp T2 ON T1.table_schema = T2.[Schema] and T1.table_name = T2.[Table] and T1.column_name = T2.[Column]
 
end

Calculate Max, Median, Min, Average, and Standard Deviation

We use dynamic SQL and unpivot techniques to calculate metrics like maximum, median, minimum, average, and standard deviation for numeric data types in each column.

--MSSQLTips.com

Declare @strTablename nvarchar(100)
Declare @strSchemaname nvarchar(100)
Declare @strQuery nvarchar(max)
Declare @strSecondaryQuery nvarchar(max)
Declare @strUnpivot nvarchar(max)
 
Truncate Table #tblTemp
 
-- Get Max values
declare @datatype nvarchar(50)
declare  csrdatatypes cursor for select distinct DATA_TYPE from INFORMATION_SCHEMA.columns where table_name = @strTablename and table_schema = @strSchemaname and (DATA_TYPE like '%int%' or DATA_TYPE like '%decimal%'  or DATA_TYPE like '%numeric%'  or DATA_TYPE like '%float%')
 
open csrdatatypes
 
fetch next from csrdatatypes into @datatype
 
while @@FETCH_STATUS = 0
begin
 
Set @strQuery = ''
Set @strSecondaryQuery = 'Select '
Set @strUnpivot = ' Unpivot ([FuncValue] for [Column] IN ('
 
Select @strQuery = ISNULL(@strQuery,'') + 'max([' + column_name + ']) as [' + column_name + '],' from INFORMATION_SCHEMA.columns where table_name = @strTablename and table_schema = @strSchemaname and DATA_TYPE = @datatype
 
Select @strUnpivot = ISNULL(@strUnpivot,'') + '[' + column_name + '] ,' from INFORMATION_SCHEMA.columns where table_name = @strTablename and table_schema = @strSchemaname and DATA_TYPE = @datatype
 
Set @strQuery = 'Select ''' + @strSchemaname + ''' AS [Schema],''' + @strTablename + ''' as [Table], [Column], [FuncValue] From (Select ' + Substring(@strQuery,1,LEN(@strQuery) -1) + ' From [' + @strSchemaname + '].[' + @strTablename + ']) As p ' + SUBSTRING(@strUnpivot,1,Len(@strUnpivot) - 1) + ')) As unpvt '
 
Insert Into #tblTemp Exec (@strQuery)
 
Update T1
Set T1.[max_value] = T2.FuncValue
FROM ##Statistics T1 inner join #tblTemp T2 ON T1.table_schema = T2.[Schema] and T1.table_name = T2.[Table] and T1.column_name = T2.[Column]
 
Set @strQuery = ''
Set @strSecondaryQuery = 'Select '
Set @strUnpivot = ' Unpivot ([FuncValue] for [Column] IN ('
 
Truncate Table #tblTemp
 
-- Get Median values
 
Set @strQuery = ''
Set @strSecondaryQuery = 'Select '
Set @strUnpivot = ' Unpivot ([FuncValue] for [Column] IN ('
 
Select @strQuery = ISNULL(@strQuery,'') + 'PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY [' + column_name + ']) OVER () as [' + column_name + '],' from INFORMATION_SCHEMA.columns where table_name = @strTablename and table_schema = @strSchemaname and DATA_TYPE = @datatype
 
Select @strUnpivot = ISNULL(@strUnpivot,'') + '[' + column_name + '] ,' from INFORMATION_SCHEMA.columns where table_name = @strTablename and table_schema = @strSchemaname and DATA_TYPE = @datatype
 
Set @strQuery = 'Select ''' + @strSchemaname + ''' AS [Schema],''' + @strTablename + ''' as [Table], [Column], [FuncValue] From (Select TOP 1 ' + Substring(@strQuery,1,LEN(@strQuery) -1) + ' From [' + @strSchemaname + '].[' + @strTablename + ']) As p ' + SUBSTRING(@strUnpivot,1,Len(@strUnpivot) - 1) + ')) As unpvt '
 
Insert Into #tblTemp Exec (@strQuery)
 
Update T1
Set T1.[median_value] = T2.FuncValue
FROM ##Statistics T1 inner join #tblTemp T2 ON T1.table_schema = T2.[Schema] and T1.table_name = T2.[Table] and T1.column_name = T2.[Column]
 
Set @strQuery = ''
Set @strSecondaryQuery = 'Select '
Set @strUnpivot = ' Unpivot ([FuncValue] for [Column] IN ('
 
Truncate Table #tblTemp
 
-- Get Min values
 
Select @strQuery = ISNULL(@strQuery,'') + 'min([' + column_name + ']) as [' + column_name + '],' from INFORMATION_SCHEMA.columns where table_name = @strTablename and table_schema = @strSchemaname and DATA_TYPE = @datatype
 
Select @strUnpivot = ISNULL(@strUnpivot,'') + '[' + column_name + '] ,' from INFORMATION_SCHEMA.columns where table_name = @strTablename and table_schema = @strSchemaname and DATA_TYPE = @datatype
 
Set @strQuery = 'Select ''' + @strSchemaname + ''' AS [Schema],''' + @strTablename + ''' as [Table], [Column], [FuncValue] From (Select ' + Substring(@strQuery,1,LEN(@strQuery) -1) + ' From [' + @strSchemaname + '].[' + @strTablename + ']) As p ' + SUBSTRING(@strUnpivot,1,Len(@strUnpivot) - 1) + ')) As unpvt '
 
Insert Into #tblTemp Exec (@strQuery)
 
Update T1
Set T1.min_value = T2.FuncValue
FROM ##Statistics T1 inner join #tblTemp T2 ON T1.table_schema = T2.[Schema] and T1.table_name = T2.[Table] and T1.column_name = T2.[Column]
 
Set @strQuery = ''
Set @strSecondaryQuery = 'Select '
Set @strUnpivot = ' Unpivot ([FuncValue] for [Column] IN ('
 
Truncate Table #tblTemp
 
-- Get Average
 
Select @strQuery = ISNULL(@strQuery,'') + 'avg([' + column_name + ']) as [' + column_name + '],' from INFORMATION_SCHEMA.columns where table_name = @strTablename and table_schema = @strSchemaname and DATA_TYPE = @datatype
 
Select @strUnpivot = ISNULL(@strUnpivot,'') + '[' + column_name + '] ,' from INFORMATION_SCHEMA.columns where table_name = @strTablename and table_schema = @strSchemaname and DATA_TYPE = @datatype
 
Set @strQuery = 'Select ''' + @strSchemaname + ''' AS [Schema],''' + @strTablename + ''' as [Table], [Column], [FuncValue] From (Select ' + Substring(@strQuery,1,LEN(@strQuery) -1) + ' From [' + @strSchemaname + '].[' + @strTablename + ']) As p ' + SUBSTRING(@strUnpivot,1,Len(@strUnpivot) - 1) + ')) As unpvt '
 
Insert Into #tblTemp Exec (@strQuery)
 
Update T1
Set T1.avg_value = T2.FuncValue
FROM ##Statistics T1 inner join #tblTemp T2 ON T1.table_schema = T2.[Schema] and T1.table_name = T2.[Table] and T1.column_name = T2.[Column]
 
Set @strQuery = ''
Set @strSecondaryQuery = 'Select '
Set @strUnpivot = ' Unpivot ([FuncValue] for [Column] IN ('
 
Truncate Table #tblTemp
 
-- Get STD
 
Select @strQuery = ISNULL(@strQuery,'') + 'Stdev([' + column_name + ']) as [' + column_name + '],' from INFORMATION_SCHEMA.columns where table_name = @strTablename and table_schema = @strSchemaname and DATA_TYPE = @datatype
 
Select @strUnpivot = ISNULL(@strUnpivot,'') + '[' + column_name + '] ,' from INFORMATION_SCHEMA.columns where table_name = @strTablename and table_schema = @strSchemaname and DATA_TYPE = @datatype
 
Set @strQuery = 'Select ''' + @strSchemaname + ''' AS [Schema],''' + @strTablename + ''' as [Table], [Column], [FuncValue] From (Select ' + Substring(@strQuery,1,LEN(@strQuery) -1) + ' From [' + @strSchemaname + '].[' + @strTablename + ']) As p ' + SUBSTRING(@strUnpivot,1,Len(@strUnpivot) - 1) + ')) As unpvt '
 
Insert Into #tblTemp Exec (@strQuery)
 
Update T1
Set T1.[stdev_value] = T2.FuncValue
FROM ##Statistics T1 inner join #tblTemp T2 ON T1.table_schema = T2.[Schema] and T1.table_name = T2.[Table] and T1.column_name = T2.[Column]
 
fetch next from csrdatatypes into @datatype
 
end
 
close csrdatatypes
deallocate csrdatatypes
 
Set @strQuery = ''
Set @strSecondaryQuery = 'Select '
Set @strUnpivot = ' Unpivot ([FuncValue] for [Column] IN ('
 
Truncate Table #tblTemp

Update Additional Metrics

In this section, we update specific metrics like whether a column is a primary key, foreign key, has constraints, is indexed, etc.

Check for Columns with Names Revealing Wrong Data Types

We use the following code to check if there is some column name that indicates a different data type than the actual one:

--MSSQLTips.com
 
update ##Statistics
set [date_type_warning] = case when data_type not like '%date%' and column_name like '%date%' then 1 else 0 end 
,[time_type_warning] = case when data_type not like '%time%'  and data_type not like '%int%' and column_name like '%time%' then 1 else 0 end

Check If Maximum Length is Over Estimated

The code below is to check if there is a significant difference between the actual maximum length and the column length specified in the metadata:

--MSSQLTips.com
 
update ##Statistics
set length_warning = case when (data_type like '%char%' and max_length is not null and max_length_reality is not null) and (max_length - max_length_reality >= (isnull(max_length,0)/4)) then 1 else 0 end

Retrieve Data Storage Information

After summarizing table structures and data, it is useful to check the storage information. Besides, we calculate the number of nulls in each column by subtracting the non-null values count (calculated previously) from the total number of rows extracted from the table metadata.

--MSSQLTips.com
 
Update t1
SET table_row_count = t2.row_count, 
    [Null_Values] = (t2.row_count - [Values_Count]),
   [table_indexes_count] = IndexCount,
   [table_total_space_KB] = TableSpaceKB,
   [table_index_space_KB] = IndexSpaceKB,
   [table_used_space_KB] = UsedSpaceKB,
   [table_unused_space_KB] = UnusedSpaceKB,
   [table_space_warning] = case when UnusedSpaceKB >= 0.25*TableSpaceKB then 1 else 0 end,
   [table_partitions_count] = PartitionCount
FROM ##Statistics t1 inner join (
SELECT 
    t.NAME AS Table_Name,
    s.NAME AS [Schema_Name],
   COUNT(DISTINCT p.partition_number) AS PartitionCount,
    SUM(p.rows) AS [Row_Count],
   COUNT(DISTINCT i.index_id) AS IndexCount,
    SUM(CASE WHEN i.index_id < 2 THEN a.total_pages ELSE 0 END) * 8 AS TableSpaceKB,
    SUM(CASE WHEN i.index_id >= 2 THEN a.total_pages ELSE 0 END) * 8 AS IndexSpaceKB,
    SUM(a.used_pages) * 8 AS UsedSpaceKB,
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
FROM 
    sys.tables t
INNER JOIN 
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN 
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN 
    sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN 
    sys.schemas s ON t.schema_id = s.schema_id
WHERE p.index_id < 2
GROUP BY 
    t.NAME, s.NAME
) t2 on t1.table_name = t2.table_name and t1.table_schema = t2.[schema_name]

Retrieve Table Structure Information

Primary Keys

First of all, we start by retrieving primary key information:

--MSSQLTips.com
 
-- Check if column is a primary key
update t1
set t1.[column_is_primary] = 1
from ##Statistics t1 inner join INFORMATION_SCHEMA.KEY_COLUMN_USAGE t2 on t1.table_name = t2.table_name and t1.column_name = t2.column_name and t1.table_schema = T2.TABLE_SCHEMA
inner join INFORMATION_SCHEMA.TABLE_CONSTRAINTS t3 on t2.CONSTRAINT_NAME = t3.CONSTRAINT_NAME and t2.CONSTRAINT_SCHEMA = t3.CONSTRAINT_SCHEMA
where t3.CONSTRAINT_TYPE = 'PRIMARY KEY'
 
-- Table having primary keys
update t1
set t1.[table_has_primary] = 1
from ##Statistics t1 inner join INFORMATION_SCHEMA.KEY_COLUMN_USAGE t2 on t1.table_name = t2.table_name  and t1.table_schema = T2.TABLE_SCHEMA
inner join INFORMATION_SCHEMA.TABLE_CONSTRAINTS t3 on t2.CONSTRAINT_NAME = t3.CONSTRAINT_NAME and t2.CONSTRAINT_SCHEMA = t3.CONSTRAINT_SCHEMA
where t3.CONSTRAINT_TYPE = 'PRIMARY KEY'

Foreign Keys

Then, we retrieve the foreign key information:

--MSSQLTips.com
 
-- column is a foreign key
update t1
set t1.[column_is_foreign] = 1
from ##Statistics t1 inner join INFORMATION_SCHEMA.KEY_COLUMN_USAGE t2 on t1.table_name = t2.table_name and t1.column_name = t2.column_name and t1.table_schema = T2.TABLE_SCHEMA
inner join INFORMATION_SCHEMA.TABLE_CONSTRAINTS t3 on t2.CONSTRAINT_NAME = t3.CONSTRAINT_NAME and t2.CONSTRAINT_SCHEMA = t3.CONSTRAINT_SCHEMA
where t3.CONSTRAINT_TYPE = 'FOREIGN KEY'

Constraints

Next, we identify columns that have constraints

--MSSQLTips.com
 
-- column has constraints
update t1
set t1.[column_has_constraints] = 1
from ##Statistics t1 inner join INFORMATION_SCHEMA.KEY_COLUMN_USAGE t2 on t1.table_name = t2.table_name and t1.column_name = t2.column_name and t1.table_schema = T2.TABLE_SCHEMA
inner join INFORMATION_SCHEMA.TABLE_CONSTRAINTS t3 on t2.CONSTRAINT_NAME = t3.CONSTRAINT_NAME and t2.CONSTRAINT_SCHEMA = t3.CONSTRAINT_SCHEMA
where t3.CONSTRAINT_TYPE NOT IN ( 'FOREIGN KEY','PRIMARY KEY')

Indexed Columns

Identity indexed columns:

--MSSQLTips.com
 
-- column is indexed
update t4
set column_is_indexed = 1
From sys.all_columns t1 inner join sys.index_columns t2 on t1.column_id = t2.column_id and t1.object_id = t2.object_id
inner join sys.indexes t3 on t3.index_id = t2.index_id and t3.object_id = t2.object_id
inner join ##Statistics t4 on t1.object_id = object_id('[' +t4.table_schema + '].[' + t4.table_name + ']') and t1.name = t4.column_name

Identity Columns

Identify identity columns:

--MSSQLTips.com
 
-- column is identity
update t2
set t2.column_is_identity = 1
from sys.all_columns t1 inner join ##Statistics t2 on t1.object_id = object_id('[' + t2.table_schema + '].[' + t2.table_name + ']') and t1.name = t2.column_name
where t1.is_identity = 1

Table Having a Clustered Index

Retrieving tables having a clustered index:

--MSSQLTips.com
 
-- table has clustered index
update t2
set t2.table_has_clustered_index = 1
from sys.indexes t1 inner join ##Statistics t2 on t1.object_id = object_id('[' + t2.table_schema + '].[' + t2.table_name + ']') 
where t1.type_desc like 'clustered%'

Replace Nulls with Zero

Finally, we replace null values in specific metrics columns with zero for better representation and clarity.

--MSSQLTips.com
 
update ##Statistics
set column_has_constraints = case when column_has_constraints = 1 then 1 else 0 end,
table_has_clustered_index = case when table_has_clustered_index = 1 then 1 else 0 end,
column_is_identity = case when column_is_identity = 1 then 1 else 0 end,
column_is_indexed = case when column_is_indexed = 1 then 1 else 0 end,
[table_has_primary] = case when [table_has_primary] = 1 then 1 else 0 end,
[column_is_foreign] = case when [column_is_foreign] = 1 then 1 else 0 end,
[column_is_primary] = case when [column_is_primary] = 1 then 1 else 0 end,
[column_is_partitioned] = case when [column_is_partitioned] = 1 then 1 else 0 end,
values_count = case is_nullable when 0 then table_row_count else values_count end,
null_values = case is_nullable when 0 then 0 else null_values end

Running the Script

Finally, we should retrieve the data from the temporary statistics table we create using the following SELECT command:

--MSSQLTips.com
select * from ##Statistics

Next Steps

Here is the entire profiling script.
In a later article, we will use the output of this script in a Power BI report to emphasize the potential warnings in our data.
It is recommended to check the SSIS Data Profiling Task to learn more about the existing data profiling methods.

About the author

Hadi Fadlallah is an SQL Server professional with more than 10 years of experience. His main expertise is in data integration. He's one of the top ETL and SQL Server Integration Services contributors at Stackoverflow.com. He holds a Ph.D. in data science focusing on context-aware big data quality and two master's degrees in computer science and business computing.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2023-09-29

SQL Script for SQL Server Table and Column Properties