Select table/field names

select a.name,b.* from sysobjects a, syscolumns b
where a.xtype=’U’ and a.id=b.id
order by a.name

a.name是指 table name
b.name是指 column name

select a.name as tablename,b.name as columename,c.name as typename, TheNULL=
case b.isnullable
when 1 then ‘YES’
when 0 then ‘NO’
end,
TheDefault=
case b.cdefault
when 0 then “
else (select text from syscomments where id=b.cdefault)
end
from sysobjects a, syscolumns b, systypes c
where a.xtype=’U’ and a.id=b.id and b.xtype=c.xtype
order by a.name

select a.name as tablename,b.name as columename,c.name + ‘(‘ + cast(c.length as varchar) + ‘)’ as typename,
TheDefault=
case b.cdefault
when 0 then
case isnull(b.autoval,0)
when 0 then “
else ‘identity’
end
else (select text from syscomments where id=b.cdefault)
end ,
TheNULL=
case b.isnullable
when 1 then ‘YES’
when 0 then ‘NO’
end

from sysobjects a, syscolumns b, systypes c
where a.xtype=’U’ and a.id=b.id and b.xtype=c.xtype
order by a.name

select a.name as tablename,b.name as columename,c.name + ‘(‘ +
case c.name
when ‘nvarchar’ then cast(b.length/2 as varchar)
else cast(b.length as varchar)
end + ‘)’ as typename,
TheDefault=
case b.cdefault
when 0 then
case isnull(b.autoval,0)
when 0 then “
else ‘identity’
end
else (select text from syscomments where id=b.cdefault)
end ,
TheNULL=
case b.isnullable
when 1 then ‘YES’
when 0 then ‘NO’
end

from sysobjects a, syscolumns b, systypes c
where a.xtype=’U’ and a.id=b.id and b.xtype=c.xtype
order by a.name

 

 

select name from master..syscolumns where id=object_id(‘master..sysprocesses’)

a 資料庫裡面的 b資料表所有欄位名稱
select name from a..syscolumns where id=object_id(‘a..b’)

a 資料庫裡面的所有資料表名稱
select * from a..sysobjects where xtype=’U’ and name<>’dtproperties’