生成实体文件 需要用到的SQL 语句

---------SQL SERVER ---------------

----- 获取用户表
select id,name from sysobjects where xtype='U'and name<>'dtproperties' order by name
---- 获取用户存储过程
select id,name from sysobjects where xtype='P'and name<>'dtproperties' order by name
--- 获取用户视图
select id,name from sysobjects where xtype='V'and name<>'dtproperties' order by name



declare  @TbName nvarchar(60)
go
set @TbName='tbname'
-----获取用户表具体结构
SELECT  
表明               =   CASE   WHEN   A.COLORDER=1   THEN   D.NAME   ELSE   ' '   END,
表说明           =   CASE   WHEN   A.COLORDER=1   THEN   ISNULL(F.VALUE, ' ')   ELSE   ' '   END,
栏位序号       =   A.COLORDER,
栏位名           =   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   PARENT_OBJ=A.ID   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.XUSERTYPE=B.XUSERTYPE
INNER   JOIN  
SYSOBJECTS   D  
ON  
A.ID=D.ID     AND   D.XTYPE= 'U '   AND     D.NAME <> 'DTPROPERTIES '
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    
LEFT   JOIN  
sys.extended_properties   F  
ON  
D.ID=F.major_id   AND   F.minor_id=0

Where D.Name=@TbName

ORDER   BY  
A.ID,A.COLORDER