Technical Article

Locate a column across databases

,

This will search for a column across all databases.

You must specifiy the exact column spelling.

After that, you can get creative. If you have a number of databases, perhaps versioned by release, you can specifiy a @Branch suffix - so by passing in 0010 you would match

MyDatabase0010

MyBills0010

MyInvoice0010.

You can specify an exact databasse via @DB, and can specify if you want to see all matches, all tables, or all views.

Examples

EXECUTE dbo.LocateColumnDefinitions @Column = 'AccountID', @Branch = '0099'

EXECUTE dbo.LocateColumnDefinitions @Column = 'Address1', @DB = 'DATASERVICES'
EXECUTE dbo.LocateColumnDefinitions @Column = 'AccountID', @ViewTableAll = 'V'
EXECUTE dbo.LocateColumnDefinitions @Column = 'InsertDate',@ViewTableAll = 'V', @Branch = '0099 
USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
------------------------------------------------------------------------------------------------------
-- Name			: LocateColumnDefinitions
-- Description	: Lists a occurrences of a column on a server or in a release
-- Example		: EXECUTE dbo.LocateColumnDefinitions @Column = 'AccountID',	@Branch = '0099'
--				  EXECUTE dbo.LocateColumnDefinitions @Column = 'Address1',	@DB = 'DATASERVICES'
--				  EXECUTE dbo.LocateColumnDefinitions @Column = 'AccountID',	@ViewTableAll = 'V'
--				  EXECUTE dbo.LocateColumnDefinitions @Column = 'InsertDate',@ViewTableAll = 'V', @Branch = '0099'
-- Called By	: This is a utility routine
--
-- Revision		Author			Description
-- 2013-05-30	Douglas Osborne	Created	
------------------------------------------------------------------------------------------------------
Create PROCEDURE [dbo].[LocateColumnDefinitions]
(
	@Column			SYSNAME,			-- Must be exact column name
	@Branch			NCHAR(4)= NULL,		-- Must be exact branch - 0099
	@DB				SYSNAME	= NULL,		-- To specify an exact database
	@ViewTableAll	CHAR(1) = ''		-- T=Only Tables, V=Only Views, else both
)
AS
BEGIN
	DECLARE
		@SQL		NVARCHAR(MAX),
		@CheckDB	NVARCHAR(100),
		@ObjectType	NVARCHAR(100)

	-- See if we specified a branch, eg AdventureWorks0010. Use this when DBs are tied to a release 
	IF @Branch IS NOT NULL
	BEGIN
		SET @CheckDB = 'DB_NAME() LIKE ''%' + @Branch + ''' AND ';
	END
	ELSE
	BEGIN
		-- See if we specified a specific database
		IF @DB IS NOT NULL
		BEGIN
			SET @CheckDB = 'DB_NAME() = ''' + @DB + ''' AND ';
		END
		ELSE
		BEGIN
			SET @CheckDB = '';
		END
	END

	-- Set the possible filter
	SET @ObjectType =	CASE @ViewTableAll
						WHEN 'T' THEN ' AND SO.Type_Desc = ''User_Table'''
						WHEN 'V' THEN ' AND SO.Type_Desc = ''View'''
						ELSE ''
						END

	-- Build the SQL
	SET @SQL = '
	USE ?

	IF ' + @CheckDB + 'EXISTS
	(
		SELECT 1
		FROM sys.columns AS SC
		INNER JOIN sys.objects AS SO ON SC.OBJECT_ID = SO.OBJECT_ID
		INNER JOIN sys.types AS ST ON SC.system_type_id = ST.system_type_id AND NOT (ST.system_type_id = 231 AND ST.name = ''nvarchar'')
		WHERE SC.Name = ''' + @Column + '''' + @ObjectType + '
	)
	BEGIN
		SELECT DB_NAME() AS [Database], OBJECT_NAME( SC.OBJECT_ID ) AS [Table], SC.name AS [Column], CASE SO.Type_Desc WHEN ''User_Table'' THEN ''Table'' ELSE ''View'' END AS [Object Type], 
		CASE ST.Name
WHEN ''VARCHAR'' THEN ''VARCHAR('' + CAST( SC.Max_Length AS VARCHAR ) + '')''
WHEN ''NVARCHAR'' THEN ''NVARCHAR('' + CAST( SC.Max_Length AS VARCHAR ) + '')''
WHEN ''CHAR'' THEN ''CHAR('' + CAST( SC.Max_Length AS VARCHAR ) + '')''
WHEN ''NCHAR'' THEN ''NCHAR('' + CAST( SC.Max_Length AS VARCHAR ) + '')''
WHEN ''INT'' THEN ''INT''
WHEN ''BIT'' THEN ''BIT''
WHEN ''BIGINT'' THEN ''BIGINT''
WHEN ''SYSNAME'' THEN ''SYSNAME''
WHEN ''DATETIME'' THEN ''DATETIME''
WHEN ''FLOAT'' THEN ''FLOAT''
WHEN ''DATE'' THEN ''DATE''
WHEN ''DECIMAL'' THEN ''DECIMAL('' + CAST( SC.Precision AS VARCHAR ) + '', '' + CAST( SC.Scale AS VARCHAR) + '')''
WHEN ''TIMESTAMP'' THEN ''TIMESTAMP''
ELSE ST.Name
END AS Type
		, CASE SC.Is_NULLable WHEN 1 THEN ''NULL'' ELSE ''NOT NULL'' END AS Nullable, 
		ISNULL( object_definition( SC.default_object_id ), '''' ) AS [Default]
		FROM sys.columns AS SC
		INNER JOIN sys.types AS ST ON SC.system_type_id = ST.system_type_id AND NOT (ST.system_type_id = 231 AND ST.name = ''nvarchar'')
		INNER JOIN sys.objects AS SO ON SC.OBJECT_ID = SO.OBJECT_ID
		INNER JOIN sys.all_objects AS AO ON SC.OBJECT_ID = AO.OBJECT_ID 
		WHERE SC.Name = N''' + @Column + '''' + @ObjectType + '
		ORDER BY SO.[Type_Desc], OBJECT_NAME( SC.OBJECT_ID )
	END'

	-- Execute against all DBs
	EXECUTE dbo.sp_msforeachdb @SQL;
END

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

4 (4)

You rated this post out of 5. Change rating

Share

Tags

Join the discussion and add your comment

Share

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

4 (4)

You rated this post out of 5. Change rating

Related content