Sometimes it is necessary to get knowledge of the usage of your table columns. This little stored procedure is looking in all tables, views, functions and stored procedures for your column. But remember only user created objects will be scanned. The stored procedure for this functionality is stored in the master database, so it can be used for all databases in your instance.
The procedure takes two parameters:
- @SEARCHSTRING – Column you are looking for
- @DB_NAME – Scanned database
USE master;
GO
IF OBJECT_ID('dbo.usp_SearchColumnUsage', 'P') IS NOT NULL
DROP PROCEDURE dbo.usp_SearchColumnUsage
GO
CREATE PROCEDURE dbo.usp_SearchColumnUsage
(
@SEARCHSTRING NVARCHAR(100) = NULL
,@DB_NAME NVARCHAR(100) = NULL
)
AS
BEGIN
DECLARE @cmd NVARCHAR(4000) =
'USE [' + LTRIM(RTRIM(@DB_NAME)) + '];
/* Searchs für Stored Procs und Functions*/
select
[SCHEMA] = sm.name
,[NAME] = so.name
,[OBJECTTYPE] = CASE so.TYPE
WHEN ''FN'' THEN ''UserDefinedFunction''
WHEN ''P'' THEN ''StoredProcedure''
ELSE ''UNKNOWN''
END
from sys.all_sql_modules s_all
JOIN sys.objects so ON s_all.object_id = so.object_id
JOIN sys.schemas sm ON sm.schema_id = so.schema_id
WHERE s_all.definition LIKE ''%' + @SearchString + '%''
UNION
/* query in Tables */
select
[SCHEMA] = t.TABLE_SCHEMA
,[NAME] = t.TABLE_NAME
,[OBJECTTYPE] = ''Table''
from INFORMATION_SCHEMA.COLUMNS c
JOIN INFORMATION_SCHEMA.TABLES t ON
c.TABLE_NAME = t.TABLE_NAME AND
c.TABLE_SCHEMA = t.TABLE_SCHEMA
WHERE c.COLUMN_NAME LIKE ''%' + @SearchString + '%''
UNION
/* query in Views */
select
[SCHEMA] = t.TABLE_SCHEMA
,[NAME] = t.TABLE_NAME
,[OBJECTTYPE] = ''View''
from INFORMATION_SCHEMA.COLUMNS c
JOIN INFORMATION_SCHEMA.VIEWS t ON
c.TABLE_NAME = t.TABLE_NAME AND
c.TABLE_SCHEMA = t.TABLE_SCHEMA
WHERE c.COLUMN_NAME LIKE ''%' + @SearchString + '%''
ORDER BY [SCHEMA], [NAME], [OBJECTTYPE]
'
EXECUTE sp_executesql @cmd
END
GO
EXECUTE master.dbo.usp_SearchColumnUsage @SEARCHSTRING = 'TABLE_SCHEMA', @DB_NAME = 'master'
Keine Kommentare:
Kommentar veröffentlichen