获取表主键
1
:
SELECT
TABLE_NAME,COLUMN_NAME
FROM
INFORMATION_SCHEMA.KEY_COLUMN_USAGEWHERE TABLE_NAME
<>
'
dtproperties
'
2 : EXEC sp_pkeys @table_name = ' 表名 '
3 :
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
表名 = 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
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 = ' 字段名称 '
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