Below is a simple solution as to how you can find certain column from multiple database. This code is simply looking thru each database and finding a keyword. On the end of this blog is a reference to sys object catalog views, it is an interesting read if you've never used this before!
Primary Reference:
DB_NAME = This function returns the name of a specified database
SCHEMA_NAME = Returns the schema name associated with a schema ID
SYS.COLUMNS = Returns a row for each column of an object that has columns, such as views or tables
SYS.TABLES = Returns a row for each user table in SQL Server
SYS.VIEWS = Contains a row for each view object, with sys.objects.type = V
LIKE = Used to compare a value to similar values using the wildcard operators
% = Matches one or more characters
Sample Result: Find Columns Like '%Name%' from multiple database
CODE:
/* Query Name: SQL Multi-database Column Search
Description: Will aide in searching for a column from multiple database
Created By: LearnwithGlezy.com
------------------------------------------------------------------------------------------------------------
Enter the expected "Column" Name
Use as if you are doing a normal "like" command without % in beginning or end
E.g.: DECLARE @ColumnSearch VARCHAR(MAX) = 'Prop%Address'
------------------------------------------------------------------------------------------------------------*/
DECLARE @ColumnSearch VARCHAR(MAX) = 'Name'
------------------------------------------------------------------------------------------------------------
--Table selection (Choose the most beneficial table(s) for your research)
-- This is important especially when the server(s) has too many tables
------------------------------------------------------------------------------------------------------------
IF OBJECT_ID('tempdb.dbo.#Databases') IS NOT NULL DROP TABLE #Databases
CREATE TABLE #Databases (DatabaseName VARCHAR(MAX))
INSERT INTO #Databases VALUES
('Learn'),
('Sample')
------------------------------------------------------------------------------------------------------------
--Create data dump table for results
------------------------------------------------------------------------------------------------------------
IF OBJECT_ID('tempdb.dbo.#AllTableSearch') IS NOT NULL DROP TABLE #AllTableSearch
CREATE TABLE #AllTableSearch (DatabaseName VARCHAR(MAX), SchemaName VARCHAR(MAX), TableName VARCHAR(MAX), ColumnName VARCHAR(MAX))
------------------------------------------------------------------------------------------------------------
--Search through DB's
------------------------------------------------------------------------------------------------------------
DECLARE @Rnk INT = 1, @DatabaseName VARCHAR(MAX) = ''
WHILE @DatabaseName IS NOT NULL
BEGIN
SET @DatabaseName = (SELECT name FROM (SELECT name, Rnk = ROW_NUMBER() OVER (ORDER BY name)
FROM sys.databases WHERE name IN (SELECT DatabaseName FROM #Databases)) x WHERE Rnk = @Rnk);
IF @DatabaseName <> '' AND @DatabaseName IS NOT NULL
EXECUTE ('
USE '+@DatabaseName+'
INSERT INTO #AllTableSearch
SELECT
DB_NAME(),
SCHEMA_NAME(schema_id),
tables.name,
sys.columns.name
FROM sys.columns
JOIN sys.tables ON sys.columns.object_id = tables.object_id
WHERE sys.columns.name like ''%'+@ColumnSearch+'%''
')
SET @Rnk = @Rnk + 1
END;
------------------------------------------------------------------------------------------------------------
--Result
------------------------------------------------------------------------------------------------------------
SELECT *,
TestColumn = CONCAT('select top 10 * from ',DatabaseName,'.',SchemaName,'.',TableName)
FROM #AllTableSearch
IMPORTANT: This query will only show result from tables. If you only have access to 'Views' then change sys.tables to sys.views.
What To Study:
ROW_NUMBER:
WHILE:
SYS OBJECT CATALOG VIEWS:
Comments