Saturday, August 17, 2013

T-SQL script to search a column in all the databases avaliable in an instance of SQL Server




Overview
We are using the [INFORMATION_SCHEMA]. [COLUMNS] view to get the information about a column and its associated tables available in an instance of SQL Server. For example, ‘SalesRegion’ is column available in 3 databases then the below stored procedure will give column name, its data types, associated tables and databases in which column exists. We are using like clause to find the matching string that can be changed to equal to (=) also based on the requirement.

CREATE PROC SearchColumnNameOnAllDatabase
(@SeacrhColumn NVARCHAR(500))
AS

/****************************************************************************
** Description: This stored procedure is used to search a particular column in all the databases
**              available in an instance of SQL Server.
*****************************************************************************/

/*Declaring variables*/
DECLARE @DatabaseName NVARCHAR(250),
            @SQLString NVARCHAR(MAX),
            @DBCount INT,
            @Min INT ,
            @DB nvarchar(250)
           

/* Declaring Table Variable */
DECLARE @DatabaseTable TABLE
(ID INT IDENTITY,
DatabaseName NVARCHAR(250))

/* Declaring Table Variable */
DECLARE @SearchColumnOnTable TABLE(
      [DatabaseName] [nvarchar](128) NULL,
      [TableSchema] [nvarchar](128) NULL,
      [TableName] [sysname] NOT NULL,
      [ColumnName] [sysname] NULL,
      [DataType] [nvarchar](128) NULL,
      [DataTypeLength] [int] NULL,
      [IS_NULLABLE] [varchar](3) NULL
)



/* Inserting distinct database names avaibale in an instance */
INSERT INTO @DatabaseTable
(DatabaseName)
SELECT DISTINCT name FROM sys.DATABASES

/* Setting the value in variable */
SET @Min = 1

/* Count of total databases available in an instance of SQL Server*/
SELECT @DBCount = COUNT(DatabaseName) FROM @DatabaseTable

/*Starting WHILE loop*/
WHILE @DBCount >= @Min
BEGIN
      /* Getting Database Name */
      SELECT @DB = DatabaseName FROM @DatabaseTable WHERE ID = @Min
      PRINT @DB
      SET @SQLString =  'SELECT [TABLE_CATALOG] AS DatabaseName
      ,[TABLE_SCHEMA] AS TableSchema
      ,[TABLE_NAME] As TableName
      ,[COLUMN_NAME] As ColumnName
      ,[DATA_TYPE] As DataType
      ,[CHARACTER_MAXIMUM_LENGTH] As DataTypeLength
      ,[IS_NULLABLE]
  FROM [' + @DB +  '].[INFORMATION_SCHEMA].[COLUMNS]
  WHERE COLUMN_NAME LIKE ' + '''' + '%' + @SeacrhColumn  + '%' + ''''
     
      /* Extraction and Insertion into Table Variable*/
      INSERT INTO @SearchColumnOnTable
      EXEC (@SQLString)
     
      SET @Min = @Min + 1
END

/* Getting Output- Coulmn name and its associated Tables in all the databases */
SELECT * FROM @SearchColumnOnTable

Query Execution and Output

1 comment: