--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

List all columns in server

list

SQL