SQL语句获取数据库中的表主键,自增列,所有列

时间:2021-12-17 04:25:14
  
  
  
获取表主键
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