Thursday, December 11, 2008

Find all tables with a specific column name within entire server

I put this query together to find all tables with specific column_name within the entire databases on a server.

Method 1:

use master
go
if(object_id('tempdb..#tmp')>0)
drop table #tmp

create table #tmp (dbname nvarchar(1000), tablename nvarchar(1000), columnname nvarchar(1000))
declare @dbname nvarchar(100), @sqlstring nvarchar(1000), @columnname nvarchar(100)
set @columnname = 'promotionid'
DECLARE dbs_name CURSOR FOR select name from sys.databases
OPEN dbs_name

FETCH NEXT FROM dbs_name INTO @dbname

WHILE @@FETCH_STATUS = 0
BEGIN
set @sqlstring = 'insert into #tmp select table_catalog, table_name, column_name from '+@dbname+'.information_schema.columns where column_name = '''+@columnname+''' '
exec sp_executesql @sqlstring
FETCH NEXT FROM dbs_name INTO @dbname
end

CLOSE dbs_name
DEALLOCATE dbs_name

select * from #tmp


Method 2:

EXEC master..sp_MSForEachDB '
IF EXISTS
(
SELECT 1
FROM [?].INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME = ''promotionid''
)
BEGIN
SELECT ''[?]'';


SELECT ''['' + TABLE_SCHEMA + '']'', ''['' + TABLE_NAME + '']''
FROM [?].INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME = ''promotionid'';
END';

No comments: