Find column in all databases on server
Need to find a column across several databases on a particular server and there were a lot of databases so used this query to build a temp table that holds the found metadata for the particular SQL Server.
Need to construct dynamic sql statement that can then be executed against all databases on the server through the use of the sp_MSForEachDB
Replace the text TABLE_TO_SEARCH_FOR
and COLUMN_YOU_WANT_TO_FIND
IF OBJECT_ID('#ColumnDetails', 'U') IS NOT NULL
DROP TABLE #ColumnDetails
CREATE TABLE #ColumnDetails (
DBaseName varchar(100),
TableSchema varchar(50),
TableName varchar(100),
ColumnName varchar(100),
DataType varchar(100),
CharMaxLength varchar(100)
)
EXEC sp_MSForEachDB @command1='USE [?];
INSERT INTO #ColumnDetails SELECT
Table_Catalog
,Table_Schema
,Table_Name
,Column_Name
,Data_Type
,Character_Maximum_Length
FROM INFORMATION_SCHEMA.COLUMNS
WHERE Table_Name like ''%TABLE_TO_SEARCH_FOR%'''
SELECT DBaseName
FROM #ColumnDetails
WHERE ColumnName IN ('COLUMN_YOU_WANT_TO_FIND')
GROUP BY DBaseName