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

No comments:
Post a Comment