I would like to list all databases with a specific name and whose column from a specific table has a specific type like DATE
To get all databases with a specific name :
SELECT [name] FROM [sysdatabases] WHERE [name] like 'myDBname_%'
To get a specific type for all columns :
select TABLE_CATALOG,TABLE_NAME, COLUMN_NAME, DATA_TYPE from myDBname_something.INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'A_SPECIFIC_TABLE_NAME'
How can I link these two requests ?
In other words, how can I get the result of my second request for each result of the first request ?
Sean Lange :
Here is another option that doesn't use any loops or cursors. I am using sys.database to generate a single select statement. Unless you have a LOT of databases this isn't going to be much, if any, performance boost over a loop based solution. But I really dislike cursors and loops and this is a fairly simple way to build dynamic sql to avoid using a loop.\n\nDECLARE @SQL NVARCHAR(MAX) = '' --need to initialize to empty string\n , @TableToFind sysname = 'YourTableName'\n\nSELECT @SQL = @SQL + 'select TABLE_CATALOG, TABLE_NAME, COLUMN_NAME, DATA_TYPE from [' + db.name + '].INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableToFind UNION ALL '\nFROM sys.databases db\nWHERE [state] = 0 --only gets online multi user databases\n AND name LIKE 'myDBname_%'\n\nIF LEN(@SQL) > 0\nBEGIN\n SELECT @SQL = LEFT(@SQL, LEN(@SQL) - 10)\n\n SELECT @SQL\n --uncomment the line below when you are comfortable the dynamic sql generated will function.\n --EXEC sp_executesql @SQL, N'@TableToFind sysname', @TableToFind = @TableToFind\nEND\n",
2017-06-21T13:25:39
Tab Alleman :
You can execute your second query as the parameter in the system proc, sp_MSforeachdb\n\nEXECUTE master.sys.sp_MSforeachdb 'select TABLE_CATALOG,TABLE_NAME, COLUMN_NAME, DATA_TYPE from [?].INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = ''A_SPECIFIC_TABLE_NAME'' '\n\n\nIf you want to filter for only certain databases, you can add an additional filter on the TABLE_CATALOG column to the second query. The TABLE_CATALOG is the database name.\n\nIf you want to make sure you don't get any empty results from databases that don't have any columns that meet the filter, you can put an IF EXISTS() before your query:\n\n EXECUTE master.sys.sp_MSforeachdb 'IF EXISTS(SELECT * from [?].INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = ''A_SPECIFIC_TABLE_NAME'')\n select TABLE_CATALOG,TABLE_NAME, COLUMN_NAME, DATA_TYPE from [?].INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = ''A_SPECIFIC_TABLE_NAME'' '\n",
2017-06-21T12:51:43