sql server常用

写于2013年春

1、查列

select
     a.id table_id,
     a.name table_name,
     b.colid column_id,
     b.name column_name,               
     c.data_type,
     c.character_maximum_length,
     cast(d.value as varchar) property
from
     sys.sysobjects a
     join sys.syscolumns b on a.id = b.id
     join information_schema.columns c on a.name = c.table_name and b.colid = c.ordinal_position
     left join sys.extended_properties d on a.id  = d.major_id and b.colid = d.minor_id
where a.xtype = 'U'
     and a.name = 'admin' 
order by b.colid

2、查表及视图

select name  from sysobjects where xtype='U'
union
select name  from sysobjects where xtype='V' 

3、字段内容默认不区分大小写