获取表主键
1 : SELECT TABLE_NAME,COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGEWHERE TABLE_NAME <> ' dtproperties '
2 : EXEC sp_pkeys @table_name = ' 表名 '
3 :select o.name as 表名,c.name as 字段名,k.colid as 字段序号,k.keyno as 索引顺序,t.name as 类型
from sysindexes i
join sysindexkeys k on i.id = k.id and i.indid = k.indid
join sysobjects o on i.id = o.id
join syscolumns c on i.id = c.id and k.colid = c.colid
join systypes t on c.xusertype = t.xusertype
where o.xtype = ' U ' and o.name = ' 要查询的表名 '
and exists ( select 1 from sysobjects where xtype = ' PK ' and parent_obj = i.id and name = i.name)
order by o.name,k.colid获取所有
SELECT
表名 = case when a.colorder = 1 then d.name else '' end ,
字段名 = a.name,
标识 = case when COLUMNPROPERTY (a.id,a.name, ' IsIdentity ' ) = 1 then ' √ ' else '' end ,
主键 = case when exists ( SELECT 1 FROM sysobjects where xtype = ' PK ' and name in (
SELECT name FROM sysindexes WHERE indid in (
SELECT indid FROM sysindexkeys WHERE id = a.id AND colid = a.colid
))) then ' √ ' else '' end ,
类型 = b.name,
占用字节数 = a.length,
长度 = COLUMNPROPERTY (a.id,a.name, ' PRECISION ' ),
小数位数 = isnull ( COLUMNPROPERTY (a.id,a.name, ' Scale ' ), 0 ),
允许空 = case when a.isnullable = 1 then ' √ ' else '' end ,
默认值 = isnull (e. text , '' ),
字段说明 = isnull (g. [ value ] , '' )
FROM syscolumns a
left join systypes b on a.xtype = b.xusertype
inner join sysobjects d on a.id = d.id and d.xtype = ' U ' and d.name <> ' dtproperties ' and d.name = ' 要查询的表名 '
left join syscomments e on a.cdefault = e.id
left join sys.extended_properties g on a.id = g.major_id and a.colid = g.minor_id and g.name = ' MS_Description '
order by a.id,a.colorder获取表自增列名称
SELECT COLUMN_NAME as 标识 FROM INFORMATION_SCHEMA.columns
WHERE TABLE_NAME = ' 表名 ' AND COLUMNPROPERTY ( OBJECT_ID ( ' 表名 ' ),COLUMN_NAME, ' IsIdentity ' ) = 1
SELECT 标识 = case when COLUMNPROPERTY ( a.id,a.name, ' IsIdentity ' ) = 1 then a.name else ' NULL ' end
FROM syscolumns a
inner join sysobjects d on a.id = d.id and d.xtype = ' U ' and d.name <> ' dtproperties '
where d.name = ' TestType ' order by a.id,a.colorder获取字段默认值
select b. text as 字段默认值
from syscolumns a left join syscomments b on a.cdefault = b.id
where a.id = object_id ( ' 表名 ' ) and a.name = ' 字段名称 '移植表： SELECT * INTO 库2.dbo.表2 FROM 库1.dbo.表1