List all columns in server
The above script can be executed on any Database on the server
--Create temp tables to store sys data
create table #DBList(id int identity (1,1), DBName nvarchar(50))
create table #TableList(id int identity (1,1), DBName nvarchar(50), TableName Nvarchar(200), columnName nvarchar(200))
create table #ColumnList(id int identity (1,1), DBName nvarchar(50), TableName Nvarchar(200), tbi int,columnName nvarchar(200))
--get a list of non system DBs
insert into #DBList(DBName)
select name from master..sysDatabases where sid !=0x01
--declare variables
declare @i int
declare @max1 int
declare @db nvarchar(50)
declare @sql nvarchar(4000)
declare @sql1 nvarchar(4000)
declare @tbi int
declare @colID int
declare @max int
declare @dbname nvarchar(400)
declare @tbid int
declare @tbl nvarchar(50)
declare @svr as nvarchar(50)
--set initial state of variables
set @i=1 --counter
set @max1 =(select max(id) from #DBList)+1 --maximum value to iterate to
set @svr= upper(@@servername) --name of server
set @colID=1
---gets a list of databases from server and populate #DBList table
while @i <@max1
begin
set @db = (select DBname from #DBList where @i=id)
set @sql ='select '''+@db+''' ,id,name from '+ @db+'..sysobjects
where xtype =''u'''
set @i=@i+1
insert into #ColumnList(DbName,tbi, TableName)
exec (@sql)
end
--get details based on column id
set @max=(select max(id) from #ColumnList)+1
while @colID < @max
begin
set @dbname=(select dbname from #ColumnList where id=+ @colID)
set @tbid=(select tbi from #ColumnList where id=+ @colID)
set @tbl=(select tablename from #ColumnList where id=+ @colID)
set @sql1='select '''+@dbname +''','''+@tbl+''' ,name from ['+@dbname +']..syscolumns
where id='+ cast(@tbid as nvarchar(20))
insert into #TableList(dbname,Tablename,columnName)
exec(@sql1)
set @colID=@colID+1
end
--Select from Temp Table
select @svr as Svr,DBName,TableName,ColumnName
from #TableList
order by DBname,tablename, columnname
--CleanUp
drop table #DBList
drop table #TableList
drop table #ColumnList