--参考以下这个或者使用sp_columns tablename
create proc dbo.mysp_columns (@tableName varchar(50)) as
begin
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].['+@tableName+']') and OBJECTPROPERTY(id, N'IsUserTable') =1)
begin
select
[Name] = a.name,
DataType = b.name,
[Size] = case b.name when 'nchar' then a.length/2 when 'nvarchar' then a.length/2 else a.length end,
NoNull = allownulls,
IsIdentity = a.status/128,
IsPK = ( select 1
from sysobjects c_obj, sysobjects t_obj, syscolumns col, master.dbo.spt_values v, sysindexes i
where c_obj.uid = user_id() and c_obj.xtype = 'PK' and t_obj.id = c_obj.parent_obj
and t_obj.xtype = 'U' and t_obj.id = col.id
and col.name = index_col(t_obj.name, i.indid,v.number)
and t_obj.id = i.id and c_obj.name = i.name
and v.number > 0 and v.number <= i.keycnt and v.type = 'P'
and t_obj.name = @tableName and col.name = a.name
) ,
[CheckTable]= ( select top 1 object_name(ref.rkeyid)
from sysobjects c_obj, sysobjects t_obj, syscolumns col, sysreferences ref
where c_obj.uid = user_id() and c_obj.xtype IN ('F ')
and t_obj.id = c_obj.parent_obj and t_obj.id = col.id
and col.colid in (ref.fkey1, ref.fkey2, ref.fkey3, ref.fkey4, ref.fkey5, ref.fkey6, ref.fkey7, ref.fkey8, ref.fkey9, ref.fkey10, ref.fkey11, ref.fkey12, ref.fkey13, ref.fkey14, ref.fkey15, ref.fkey16)
and c_obj.id = ref.constid and t_obj.name = @TableName and col.name = a.name
)
from syscolumns a
join systypes b on a.xusertype=b.xusertype
where id=object_id(@tableName)
order by a.colorder
end
else
Print 'ERROR: No table named '+@tableName + ' found in ' + db_name() + ' database.'
end
go
create proc dbo.mysp_columns (@tableName varchar(50)) as
begin
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].['+@tableName+']') and OBJECTPROPERTY(id, N'IsUserTable') =1)
begin
select
[Name] = a.name,
DataType = b.name,
[Size] = case b.name when 'nchar' then a.length/2 when 'nvarchar' then a.length/2 else a.length end,
NoNull = allownulls,
IsIdentity = a.status/128,
IsPK = ( select 1
from sysobjects c_obj, sysobjects t_obj, syscolumns col, master.dbo.spt_values v, sysindexes i
where c_obj.uid = user_id() and c_obj.xtype = 'PK' and t_obj.id = c_obj.parent_obj
and t_obj.xtype = 'U' and t_obj.id = col.id
and col.name = index_col(t_obj.name, i.indid,v.number)
and t_obj.id = i.id and c_obj.name = i.name
and v.number > 0 and v.number <= i.keycnt and v.type = 'P'
and t_obj.name = @tableName and col.name = a.name
) ,
[CheckTable]= ( select top 1 object_name(ref.rkeyid)
from sysobjects c_obj, sysobjects t_obj, syscolumns col, sysreferences ref
where c_obj.uid = user_id() and c_obj.xtype IN ('F ')
and t_obj.id = c_obj.parent_obj and t_obj.id = col.id
and col.colid in (ref.fkey1, ref.fkey2, ref.fkey3, ref.fkey4, ref.fkey5, ref.fkey6, ref.fkey7, ref.fkey8, ref.fkey9, ref.fkey10, ref.fkey11, ref.fkey12, ref.fkey13, ref.fkey14, ref.fkey15, ref.fkey16)
and c_obj.id = ref.constid and t_obj.name = @TableName and col.name = a.name
)
from syscolumns a
join systypes b on a.xusertype=b.xusertype
where id=object_id(@tableName)
order by a.colorder
end
else
Print 'ERROR: No table named '+@tableName + ' found in ' + db_name() + ' database.'
end
go
或着对表生成SQL脚本