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
--
-- RevisionAuthorDescription
-- 2013-05-30Douglas OsborneCreated
------------------------------------------------------------------------------------------------------
Create PROCEDURE [dbo].[LocateColumnDefinitions]
(
@ColumnSYSNAME,-- Must be exact column name
@BranchNCHAR(4)= NULL,-- Must be exact branch - 0099
@DBSYSNAME= NULL,-- To specify an exact database
@ViewTableAllCHAR(1) = ''-- T=Only Tables, V=Only Views, else both
)
AS
BEGIN
DECLARE
@SQLNVARCHAR(MAX),
@CheckDBNVARCHAR(100),
@ObjectTypeNVARCHAR(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

Join the discussion and add your comment

Share

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

4 (4)

You rated this post out of 5. Change rating

Related content