First if you just want to know how one column is used you want to use the UI or check out my other entry. If however you want to scan your entire database and determine the dependencies (usage) of each and every column in the database then stay here. The code found in either of these places will only look at the database references (stored procedures, functions, views, triggers). This means if you are using something like SSIS, LINQ, Entity Framework, embedded SQL in your code, etc you will need to check these area on your own.
With that said, I really just continued the thought from my other entry (same as the one I noted above). The first step is to convert the stored procedure used there to one that just dumps the data to a table for later querying. I call this table ColumnUsage. I also add a TagName to the table so that you can easily identify your results among other results in that table. That way you can compare results over time, or just support multiple users. The table will be created the first time the stored procedure is executed.
Here is the new stored proc
/****** Object: StoredProcedure [dbo].[UTL_07_WriteColumnUsageToColumnUsageTable] Script Date: 06/21/2011 15:25:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
alter PROCEDURE [dbo].[UTL_07_WriteColumnUsageToColumnUsageTable]
@vcTableName varchar(100),
@vcColumnName varchar(100),
@tagName varchar(100)
AS
/************************************************************************************************
DESCRIPTION: writes all stored procedures, views, triggers
and user-defined functions that reference the
table/column passed into this proc into the ColumnUsage table.
PARAMETERS:
@vcTableName - table containing searched column
@vcColumnName - column being searched for
@tagName - a name you make up to so you can later query for your results
This procedure must be installed in the database where it will
be run due to it's use of database system tables.
USAGE:
UTL_07_WriteColumnUsageToColumnUsageTable 'schema.tablename', 'columnname', 'tagName'
AUTHOR: Karen Gayda
DATE: 07/19/2007
MODIFICATION HISTORY:
WHO DATE DESCRIPTION
--- ---------- -------------------------------------------
Brent Vermilion 06.21.2011 Recreated such that writes to a table instead of output
*************************************************************************************************/
SET NOCOUNT ON
IF (NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'ColumnUsage'))
BEGIN
CREATE TABLE dbo.ColumnUsage
(
ID int NOT NULL IDENTITY (1, 1),
TableName varchar(500) NOT NULL,
ColumnName varchar(500) NOT NULL,
UsedByType varchar(50) NOT NULL,
UsedByName varchar(2000) NOT NULL,
TagName varchar(100) NOT NULL
) ON [PRIMARY]
ALTER TABLE dbo.ColumnUsage ADD CONSTRAINT
PK_ColumnUsage PRIMARY KEY CLUSTERED
(
ID
) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
END
insert into dbo.ColumnUsage(TableName, ColumnName, UsedByType, TagName, UsedByName)
SELECT DISTINCT @vcTableName, @vcColumnName, 'Stored Procedure', @tagName, SUBSTRING(o.NAME,1,60) AS [Procedure Name]
FROM sysobjects o
INNER JOIN syscomments c
ON o.ID = c.ID
WHERE o.XTYPE = 'P'
AND c.Text LIKE '%' + @vcColumnName + '%' + @vcTableName + '%'
ORDER BY [Procedure Name]
insert into dbo.ColumnUsage(TableName, ColumnName, UsedByType, TagName, UsedByName)
SELECT DISTINCT @vcTableName, @vcColumnName, 'View', @tagName, SUBSTRING(o.NAME,1,60) AS [View Name]
FROM sysobjects o
INNER JOIN syscomments c
ON o.ID = c.ID
WHERE o.XTYPE = 'V'
AND c.Text LIKE '%' + @vcColumnName + '%' + @vcTableName + '%'
ORDER BY [View Name]
insert into dbo.ColumnUsage(TableName, ColumnName, TagName, UsedByName, UsedByType)
SELECT DISTINCT @vcTableName, @vcColumnName, @tagName, SUBSTRING(o.NAME,1,60) AS [Function Name],
CASE WHEN o.XTYPE = 'FN' THEN 'Scalar Function'
WHEN o.XTYPE = 'IF' THEN 'Inline Function'
WHEN o.XTYPE = 'TF' THEN 'Table Function'
ELSE '? Function'
END
as [Function Type]
FROM sysobjects o
INNER JOIN syscomments c
ON o.ID = c.ID
WHERE o.XTYPE IN ('FN','IF','TF')
AND c.Text LIKE '%' + @vcColumnName + '%' + @vcTableName + '%'
ORDER BY [Function Name]
insert into dbo.ColumnUsage(TableName, ColumnName, UsedByType, TagName, UsedByName)
SELECT DISTINCT @vcTableName, @vcColumnName, 'Trigger', @tagName, SUBSTRING(o.NAME,1,60) AS [Trigger Name]
FROM sysobjects o
INNER JOIN syscomments c
ON o.ID = c.ID
WHERE o.XTYPE = 'TR'
AND c.Text LIKE '%' + @vcColumnName + '%' + @vcTableName + '%'
ORDER BY [Trigger Name]
Now that we have that we can write another stored proc that will loop over every column in our database and call the above stored procedure for each column. Please note, depending on how complex your schema, the number of stored procedures, number of columns, etc this query could take quite a long time. Here is the stored proc to scan your database.
/****** Object: StoredProcedure [dbo].[UTL_08_WriteAllColumnUsageToColumnUsageTable] Script Date: 06/21/2011 15:25:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
alter PROCEDURE [dbo].[UTL_08_WriteAllColumnUsageToColumnUsageTable]
@TagName varchar(100)
AS
/************************************************************************************************
DESCRIPTION: writes all stored procedures, views, triggers
and user-defined functions that reference the
table/column passed into this proc into the ColumnUsage table.
PARAMETERS:
@TagName - a name you make up to so you can later query for your results
This procedure must be installed in the database where it will
be run due to it's use of database system tables.
USAGE:
UTL_08_WriteAllColumnUsageToColumnUsageTable 'tagName'
AUTHOR: Brent Vermilion
DATE: 06/21/2011
*************************************************************************************************/
Declare @TableName as nvarchar(512)
Declare @ColumnName as nvarchar(300)
Declare ColumnCursor CURSOR FAST_FORWARD FOR
SELECT TABLE_SCHEMA + '.' + TABLE_NAME, COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS order by TABLE_SCHEMA, TABLE_NAME, ORDINAL_POSITION, COLUMN_NAME
OPEN ColumnCursor
FETCH NEXT FROM ColumnCursor
into @TableName, @ColumnName
WHILE @@FETCH_STATUS = 0
BEGIN
-- do row specific stuff here
exec UTL_07_WriteColumnUsageToColumnUsageTable @TableName, @ColumnName, @TagName
FETCH NEXT FROM ColumnCursor
into @TableName, @ColumnName
END
CLOSE ColumnCursor
DEALLOCATE ColumnCursor
After executing the either of the stored procedure on your database you can answer some very useful questions.
- What columns are not being used?
- What is using a particular column?
select * from ColumnUsage where TableName = 'MyTableHere' and ColumnName = 'MyColumnHere'
select distinct allcols.TABLE_SCHEMA + '.' + allcols.TABLE_NAME, COLUMN_NAME, UsedByName from
INFORMATION_SCHEMA.COLUMNS allcols
left outer join ColumnUsage usage
on (allcols.TABLE_SCHEMA + '.' + allcols.TABLE_NAME = usage.TableName and allcols.COLUMN_NAME = usage.ColumnName)
where UsedByName is null
No comments:
Post a Comment