March 6, 2023 at 3:46 pm
I am looking to get a list of un-used columns in all tables in a database. I would like the output to look like something like this.
DatabaseName, SchemaName, TableName, ColumnName, etc
If anyone has a handy script or any link, please share. Thanks.
Regards,
SQLisAwe5oMe.
March 6, 2023 at 4:35 pm
And how do you define "un-used", what are your criteria for this?
March 6, 2023 at 6:19 pm
My understanding from app team is, historically they created additional columns for future use, but never used....so, I am doing this as part of a clean up process where I need to identify any columns that was never used and then have them reviewed it before deleting them.
Hope it makes sense.
Regards,
SQLisAwe5oMe.
March 6, 2023 at 6:37 pm
Is there a reason the app team can't tell you which columns are unused?
You could use server-side trace or extended events to log executions over a period long enough to make you comfortable they aren't used (watch out for end-of-month- & end-of-year-only activity) , & compare that history to existing procedures.
This DMV approach from https://dba.stackexchange.com/questions/47025/identifying-unused-stored-procedures will only reflect history since the instance was restarted/data was flushed:
SELECT p.name AS 'SP Name' -- Get list of all SPs in the current database
FROM sys.procedures AS p
WHERE p.is_ms_shipped = 0
EXCEPT
SELECT p.name AS 'SP Name' -- Get list of all SPs from the current database
FROM sys.procedures AS p -- that are in the procedure cache
INNER JOIN sys.dm_exec_procedure_stats AS qs
ON p.object_id = qs.object_id
WHERE p.is_ms_shipped = 0;
March 6, 2023 at 6:38 pm
Are they looking for columns that are entirely null? or not null, but entirely populated by a default value?Or columns that are not referenced by code?
March 6, 2023 at 11:49 pm
Here is a sample script..... to get column usage metrics.
Replace DBNAME with your database name.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[usp_Capture_TableColumnUsageMetrics] (@TableName nvarchar(100))
as
begin
SET NOCOUNT ON
/*Declare variables */
DECLARE @DatabaseName NVARCHAR(255) = N'DBName'
DECLARE @SchemaName NVARCHAR(255) = N'dbo'
DECLARE @ColumnType nvarchar(15)
DECLARE @SQLString NVARCHAR(MAX) = N''
DECLARE @ParamDefinition NVARCHAR(MAX) = N''
DECLARE @ParamDefinition1 NVARCHAR(MAX) = N''
DROP TABLE IF EXISTS #ColumnList
/* Declare a temp table with required metrics for to capture count(s) and percentages to check overall usage */
CREATE TABLE #ColumnList (
ColumnId INT IDENTITY (1, 1)
,ColumnName NVARCHAR(255)
,ColumnNullCount INT
,ColumnNullPercentage NUMERIC(10, 2)
,ColumnSingleSpaceCount INT
,ColumnSingleSpacePercentage NUMERIC(10, 2)
,ColumnMinusOneCount INT
,ColumnMinusOnePercentage NUMERIC(10, 2)
,ColumnDefaultDateCount INT
,ColumnDefaultDatePercentage NUMERIC(10, 2)
)
/* To get the row count in a table to calculate what percentage the data is filled */
DECLARE @TableRecordCount INT
/* To get the No. of columns in a table */
DECLARE @ColumnCount INT = 0
/* declare variables to capture metrics */
DECLARE @ColumnNullCount INT
DECLARE @ColumnSingleSpaceCount INT
DECLARE @ColumnMinusOneCount INT
DECLARE @ColumnDefaultDateCount INT
DECLARE @SQLColumnType NVARCHAR(1005)
DECLARE @LoopCounter INT = 1
DECLARE @ColumnName NVARCHAR(255)
/* Generate a dynamic SQL to get the row count of a table */
SET @SQLString = N'SELECT @TableRecordCount = COUNT(*) FROM '
+ @DatabaseName + N'.' + @SchemaName + N'.' + @TableName
SET @ParamDefinition = N'@TableRecordCount INT OUTPUT'
EXECUTE sp_executesql @SQLString ,@ParamDefinition ,@TableRecordCount OUTPUT
/*Capture all the column names from a table */
SET @SQLString = N'SELECT COLUMN_NAME FROM '
+ @DatabaseName + N'.' + N'INFORMATION_SCHEMA.COLUMNS
WHERE
TABLE_SCHEMA = @SchemaName
AND TABLE_NAME = @TableName'
SET @ParamDefinition = N'@SchemaName NVARCHAR(255), @TableName NVARCHAR(255)'
/*Insert all the columns in table */
INSERT INTO #ColumnList (ColumnName)
EXECUTE sp_executesql @SQLString ,@ParamDefinition ,@SchemaName ,@TableName
SELECT
@ColumnCount = COUNT(*)
FROM #ColumnList
WHILE (@LoopCounter <= @ColumnCount)
BEGIN
SELECT
@ColumnName = ColumnName
FROM #ColumnList
WHERE ColumnId = @LoopCounter
/* Check For NULL Count */
SET @SQLString =
N'SELECT @ColumnNullCount = COUNT(*) FROM '
+ @DatabaseName + N'.' + @SchemaName + N'.' + @TableName
+ N' WHERE ' + @ColumnName + N' IS NULL'
SET @ParamDefinition = N'@ColumnNullCount INT OUTPUT'
EXECUTE sp_executesql @SQLString ,@ParamDefinition,@ColumnNullCount OUTPUT
/* ColumnNullCount and percentage */
UPDATE #ColumnList
SET ColumnNullCount = @ColumnNullCount
,ColumnNullPercentage = CASE WHEN @TableRecordCount <> 0 then
@ColumnNullCount * 100.0 / @TableRecordCount
ELSE 0 END
WHERE ColumnId = @LoopCounter
/*Get data type for the selected column */
SET @SQLColumnType = N'SELECT @ColumnType = DATA_TYPE
from DBNAME.INFORMATION_SCHEMA.COLUMNS C
inner join DBNAME.INFORMATION_SCHEMA.TABLES T on c.TABLE_NAME = T.TABLE_NAME
and T.TABLE_NAME = ''' + @TableName + ''' and C.COLUMN_NAME = ''' + @ColumnName + ''' '
SET @ParamDefinition1 = N'@ColumnType nvarchar(20) OUTPUT'
EXEC sp_executesql @SQLColumnType ,@ParamDefinition1,@ColumnType OUTPUT
IF @ColumnType IN ('nvarchar', 'nchar', 'char', 'varchar')
BEGIN
/* Check and update single space Count */
SET @SQLString = N'SELECT @ColumnSingleSpaceCount = COUNT(*) FROM '
+ @DatabaseName + N'.' + @SchemaName + N'.' + @TableName
+ N' WHERE ' + @ColumnName + N' ='' '' '
SET @ParamDefinition = N'@ColumnSingleSpaceCount INT OUTPUT'
EXECUTE sp_executesql @SQLString ,@ParamDefinition ,@ColumnSingleSpaceCount OUTPUT
UPDATE #ColumnList
SET ColumnSingleSpaceCount = @ColumnSingleSpaceCount
,ColumnSingleSpacePercentage = CASE WHEN @TableRecordCount <> 0 then
@ColumnSingleSpaceCount * 100.0 / @TableRecordCount
ELSE 0 END
WHERE ColumnId = @LoopCounter
END
IF @ColumnType IN ('int','bit')
BEGIN
/* Check for negative values and update the table */
SET @SQLString = N'SELECT @ColumnMinusOneCount = COUNT(*) FROM '
+ @DatabaseName + N'.' + @SchemaName + N'.' + @TableName
+ N' WHERE ' + @ColumnName + N' < 0 '
SET @ParamDefinition = N'@ColumnMinusOneCount INT OUTPUT'
EXECUTE sp_executesql @SQLString ,@ParamDefinition ,@ColumnMinusOneCount OUTPUT
UPDATE #ColumnList
SET ColumnMinusOneCount = @ColumnMinusOneCount
,ColumnMinusOnePercentage = CASE WHEN @TableRecordCount <> 0 then
@ColumnMinusOneCount * 100.0 / @TableRecordCount
ELSE 0 END
WHERE ColumnId = @LoopCounter
END
IF @ColumnType IN ('datetime', 'datetime2')
BEGIN
/* Check for default date values like 01-01-1900 and update the table */
SET @SQLString = N'SELECT @ColumnDefaultDateCount = COUNT(*) FROM '
+ @DatabaseName + N'.' + @SchemaName + N'.' + @TableName
+ N' WHERE ' + @ColumnName + N' = ''01-01-1900'' '
SET @ParamDefinition = N'@ColumnDefaultDateCount INT OUTPUT'
EXECUTE sp_executesql @SQLString ,@ParamDefinition ,@ColumnDefaultDateCount OUTPUT
UPDATE #ColumnList
SET ColumnDefaultDateCount = @ColumnDefaultDateCount
,ColumnDefaultDatePercentage = CASE WHEN @TableRecordCount <> 0 then
@ColumnDefaultDateCount * 100.0 / @TableRecordCount
ELSE 0 END
WHERE ColumnId = @LoopCounter
END
SET @LoopCounter += 1
END
/* Insert the data into a table */
SELECT
@TableName AS [TableName]
,ColumnName AS [TableColumnName]
,@TableRecordCount AS [TableRowCount]
,ColumnNullCount AS [ColumnNullCount]
,ColumnNullPercentage AS [ColumnNullPercentage]
,ColumnSingleSpaceCount AS [ColumnSingleSpaceCount]
,ColumnSingleSpacePercentage AS [ColumnSingleSpacePercentage]
,ColumnMinusOneCount AS [ColumnMinusOneCount]
,ColumnMinusOnePercentage AS [ColumnMinusOnePercentage]
,ColumnDefaultDateCount AS [ColumnDefaultDateCount]
,ColumnDefaultDatePercentage AS [ColumnDefaultDatePercentage]
,(@TableRecordCount - (ISNULL(ColumnNullCount, 0) + ISNULL(ColumnSingleSpaceCount, 0) +
ISNULL(ColumnMinusOneCount, 0) + ISNULL(ColumnDefaultDateCount, 0))) AS [TotalColumnFilledCount]
INTO [dbo].[TableUsageMetrics]
FROM #ColumnList
ORDER BY [ColumnName]
END
=======================================================================
March 7, 2023 at 5:05 am
Is there a reason the app team can't tell you which columns are unused?
I'm glad someone else said that. If they're the ones that are going to review the findings, it must mean that they have a way to confirm it so let them use that method to find the columns to begin with.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 7, 2023 at 2:48 pm
I don't believe we allow for null in our data. I would image it would be any that have all spaces(any character fields with the default space).
Regards,
SQLisAwe5oMe.
March 7, 2023 at 3:06 pm
It could also have a default like GETDATE() or some other non-null, non-blank fixed value. That's why I'm thinking that the folks that asked the question are the ones best equipped to answer the question. Have THEM produce a list of all the columns that they know are good and then change the names of all the other columns to "XX" + the original column name as a "scream-test". It beats the hell out of actually dropping an "unused" column that's actually used by something somewhere. After at least one quarterly cycle has passed, then you're probably safe to actually drop the columns remaining that start with "XX".
Don't forget that, at the very least, you're going to need to rebuild Heaps and Clustered Indexes to recover the space after the column drops.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 7, 2023 at 3:17 pm
Thanks Jeff, yes, this is our plan....identify the columns first then rename it and monitor for sometime and if still not being used.....drop them as part of clean up.
Regards,
SQLisAwe5oMe.
April 11, 2023 at 2:57 pm
Hi Emperor100, Can you plz explain this SP as to how it works....Do I need the pass the @TableName as value or DB name?
Regards,
SQLisAwe5oMe.
April 11, 2023 at 9:07 pm
Hi Emperor100, Can you plz explain this SP as to how it works....Do I need the pass the @TableName as value or DB name?
Before creating the SP in a database, Set the two variables @DatabaseName and @SchemaName to your custom names and then create the SP. During the SP call, pass on the TableName.
It loops through all the columns of a table, checks the data type (int, char/varchar, date) and captures the following and inserts into a temporary table and outputs the results:
Hope that helps!!
=======================================================================
April 11, 2023 at 9:30 pm
Thank you Emperor100 for details.
Regards,
SQLisAwe5oMe.
April 12, 2023 at 7:29 pm
I got the below script to find out unused columns in a database and the output looks good to me.....can someone help in adding 'DatabaseName' to this script?
DatabaseName, TableSchema, TableName and ColumnName
If object_id('tempdb..#Results') is not null
Drop Table #Results;
GO
Create Table #Results
(
TableSchema sysname not null
, TableName sysname not null
, ColumnName sysname not null
);
GO
Declare @TableSchema sysname;
Declare @TableName sysname;
Declare @ColumnName sysname;
Declare @DataType sysname;
Declare @Columns Cursor;
Declare @BaseSql nvarchar(max);
Declare @Sql nvarchar(max);
Declare @AdditionalFilter nvarchar(max);
Set @BaseSql = 'Insert #Results( TableSchema, TableName, ColumnName )
Select ''TABLE_SCHEMA'', ''TABLE_NAME'', ''COLUMN_NAME''
From ( Select 1 As V ) As Z
Where Exists (
Select 1
From [TABLE_SCHEMA].[TABLE_NAME]
)
And Not Exists (
Select 1
From [TABLE_SCHEMA].[TABLE_NAME]
Where [COLUMN_NAME] Is Not Null
ADDITIONAL_FILTER
)';
Set @Columns = Cursor Fast_Forward For
Select C.TABLE_SCHEMA, C.TABLE_NAME, C.COLUMN_NAME, C.DATA_TYPE
From INFORMATION_SCHEMA.COLUMNS As C
Left Join INFORMATION_SCHEMA.VIEWS As V
On V.TABLE_SCHEMA = C.TABLE_SCHEMA
And V.TABLE_NAME = C.TABLE_NAME
Where V.TABLE_NAME Is Null;
Open @Columns;
Fetch Next From @Columns Into @TableSchema, @TableName, @ColumnName, @DataType;
While @@Fetch_Status = 0
Begin
If @DataType In('int','smallint','tinyint','bigint','numeric','bit','decimal','money','smallmoney','float','real')
Set @AdditionalFilter = 'And [COLUMN_NAME] <> 0';
Else If @DataType In('char','nchar','varchar','nvarchar','text','ntext')
Set @AdditionalFilter = 'And Len([COLUMN_NAME]) > 0';
Else
Set @AdditionalFilter = '';
Set @Sql = Replace(@BaseSql, 'ADDITIONAL_FILTER', @AdditionalFilter);
Set @Sql = Replace(@Sql, 'TABLE_SCHEMA', @TableSchema);
Set @Sql = Replace(@Sql, 'TABLE_NAME', @TableName);
Set @Sql = Replace(@Sql, 'COLUMN_NAME', @ColumnName);
--Print @Sql
Exec(@Sql)
Fetch Next From @Columns Into @TableSchema, @TableName, @ColumnName, @DataType;
End
Close @Columns;
Deallocate @Columns;
Select *
From #Results
Regards,
SQLisAwe5oMe.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply