I am looking at about 200 databases, each have an, almost, uniform structure.
I am trying to search all the databases for 1 specific column in 1 specific table. If that column does not exist, then return the Database name.
I wrote a cursor to do this, but I am stuck on how to return the Database name.
set nocount on;
DECLARE @db_name NVARCHAR (150)
DECLARE c_db_names CURSOR FOR
SELECT name
FROM sys.databases
WHERE name like ('B1%') --might need to exclude more dbs
OPEN c_db_names
FETCH c_db_names INTO @db_name
WHILE @@Fetch_Status = 0
begin
Exec('
SELECT 'code for returning DB name goes here'
,c.name AS 'ColumnName'
,t.name AS 'TableName'
FROM '+ @db_name + '.sys.columns c
JOIN '+ @db_name + '. sys.tables t ON c.object_id = t.object_id
WHERE 1=1
and c.name like %MyName%'
')
FETCH c_db_names INTO @db_name
END
CLOSE c_db_names
DEALLOCATE c_db_names
Any recommendations here or even thoughts on how to do this more efficiently? i am not a fan of cursors, but they have their uses.
Also, how the heck do I flip that "LIKE" to something more reasonable for what I want?