List all columns in 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