得到sqlserver數(shù)據(jù)庫中表結(jié)構(gòu)信息

/* create by zhanglei 20061218?
得到用戶的表結(jié)構(gòu)信息?
?
forexample??
select * from vbyhbjg?
*/?
?
alter view VBYHBJG?
as?
select o.name 表名稱, '' 表描述, isnull(col.name,'') 列名稱, isnull(prt.value,'') 列描述 ,?
?????? t.name? +'(' +convert(varchar(5),col.length)+')' 表類型 ,
?????? case col.isnullable when 1 then '空' else '非空' end as [是否為空] ,'' 默認(rèn)值, '' 備注?
??????
from sysobjects o right join??
???? syscolumns col on o.id = col.id left join?
???? sysproperties prt on col.id = prt.id and col.colid = prt.smallid left join??
???? systypes t on col.xusertype = t.xusertype??
where o.xtype = 'U'???
? ?

?

--執(zhí)行測試
CREATE TABLE dbo.Table1
?(
?FID varchar(50) NULL,
?FName varchar(50) NULL
?)? ON [PRIMARY]
GO
DECLARE @v sql_variant
SET @v = N'編號(hào)'
EXECUTE sp_addextendedproperty N'MS_Description', @v, N'user', N'dbo', N'table', N'Table1', N'column', N'FID'
GO
DECLARE @v sql_variant
SET @v = N'名稱'
EXECUTE sp_addextendedproperty N'MS_Description', @v, N'user', N'dbo', N'table', N'Table1', N'column', N'FName'
GO
ALTER TABLE dbo.Table1 ADD CONSTRAINT
?DF_Table1_FID DEFAULT 'getID' FOR FID
GO
ALTER TABLE dbo.Table1 ADD CONSTRAINT
?DF_Table1_FName DEFAULT 'getName' FOR FName
Go

--查詢表結(jié)構(gòu)

select * from vbyhbjg

--返回結(jié)果

Table1??FID?編號(hào)?varchar(50)?空??
Table1??FName?名稱?varchar(50)?空?