public static string GetCommand(string p_Table) { StringBuilder _SqlCommand = new StringBuilder(); _SqlCommand.Append("SELECT"); //_SqlCommand.Append(" a.colorder N'字段序号',"); _SqlCommand.Append(" a.name N'字段名',"); _SqlCommand.Append(" (case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then 'YES' else 'NO' end) N'标识',"); _SqlCommand.Append(" (case when (SELECT count(*)"); _SqlCommand.Append(" FROM sysobjects"); _SqlCommand.Append(" WHERE (name in"); _SqlCommand.Append(" (SELECT name"); _SqlCommand.Append(" FROM sysindexes"); _SqlCommand.Append(" WHERE (id = a.id) AND (indid in "); _SqlCommand.Append(" (SELECT indid"); _SqlCommand.Append(" FROM sysindexkeys"); _SqlCommand.Append(" WHERE (id = a.id) AND (colid in"); _SqlCommand.Append(" (SELECT colid"); _SqlCommand.Append(" FROM syscolumns "); _SqlCommand.Append(" WHERE (id = a.id) AND (name = a.name))))))) AND"); _SqlCommand.Append(" (xtype = 'PK'))>0 then 'YES' else 'NO' end) N'主键',"); _SqlCommand.Append(" (case when a.isnullable=1 then 'YES'else 'NO' end) N'允许空',"); _SqlCommand.Append(" b.name N'类型',"); _SqlCommand.Append(" COLUMNPROPERTY(a.id,a.name,'PRECISION') as N'长度',"); _SqlCommand.Append(" isnull(g.[value],'') AS N'字段说明'"); //_SqlCommand.Append(" isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0) as N'小数位数',"); //_SqlCommand.Append(" a.length N'占用字节数',"); //_SqlCommand.Append(" isnull(e.text,'') N'默认值',"); _SqlCommand.Append(" FROM syscolumns a left join systypes b "); _SqlCommand.Append(" on a.xtype=b.xusertype"); _SqlCommand.Append(" inner join sysobjects d"); _SqlCommand.Append(" on a.id=d.id and d.xtype='U' and d.name<>'dtproperties'"); _SqlCommand.Append(" left join syscomments e"); _SqlCommand.Append(" on a.cdefault=e.id"); _SqlCommand.Append(" left join sysproperties g"); _SqlCommand.Append(" on a.id=g.id AND a.colid = g.smallid"); _SqlCommand.Append(" where d.Name='" + p_Table + "'"); _SqlCommand.Append(" order by object_name(a.id),a.colorder"); return _SqlCommand.ToString(); }执行最后的SQL语句看看是不是你想要的
{
StringBuilder _SqlCommand = new StringBuilder();
_SqlCommand.Append("SELECT");
//_SqlCommand.Append(" a.colorder N'字段序号',");
_SqlCommand.Append(" a.name N'字段名',");
_SqlCommand.Append(" (case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then 'YES' else 'NO' end) N'标识',");
_SqlCommand.Append(" (case when (SELECT count(*)");
_SqlCommand.Append(" FROM sysobjects");
_SqlCommand.Append(" WHERE (name in");
_SqlCommand.Append(" (SELECT name");
_SqlCommand.Append(" FROM sysindexes");
_SqlCommand.Append(" WHERE (id = a.id) AND (indid in ");
_SqlCommand.Append(" (SELECT indid");
_SqlCommand.Append(" FROM sysindexkeys");
_SqlCommand.Append(" WHERE (id = a.id) AND (colid in");
_SqlCommand.Append(" (SELECT colid");
_SqlCommand.Append(" FROM syscolumns ");
_SqlCommand.Append(" WHERE (id = a.id) AND (name = a.name))))))) AND");
_SqlCommand.Append(" (xtype = 'PK'))>0 then 'YES' else 'NO' end) N'主键',");
_SqlCommand.Append(" (case when a.isnullable=1 then 'YES'else 'NO' end) N'允许空',");
_SqlCommand.Append(" b.name N'类型',");
_SqlCommand.Append(" COLUMNPROPERTY(a.id,a.name,'PRECISION') as N'长度',");
_SqlCommand.Append(" isnull(g.[value],'') AS N'字段说明'"); //_SqlCommand.Append(" isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0) as N'小数位数',");
//_SqlCommand.Append(" a.length N'占用字节数',");
//_SqlCommand.Append(" isnull(e.text,'') N'默认值',"); _SqlCommand.Append(" FROM syscolumns a left join systypes b ");
_SqlCommand.Append(" on a.xtype=b.xusertype");
_SqlCommand.Append(" inner join sysobjects d");
_SqlCommand.Append(" on a.id=d.id and d.xtype='U' and d.name<>'dtproperties'");
_SqlCommand.Append(" left join syscomments e");
_SqlCommand.Append(" on a.cdefault=e.id");
_SqlCommand.Append(" left join sysproperties g");
_SqlCommand.Append(" on a.id=g.id AND a.colid = g.smallid");
_SqlCommand.Append(" where d.Name='" + p_Table + "'");
_SqlCommand.Append(" order by object_name(a.id),a.colorder");
return _SqlCommand.ToString();
}执行最后的SQL语句看看是不是你想要的
CREATE TABLE [LR_FindJop] (
[FJ_ID] [int] IDENTITY (1, 1) NOT NULL ,
[FJ_Title] [varchar] (100) NULL ,
[FJ_Content] [text] NULL ,
[FJ_Num] [int] NULL ,
[FJ_JopType] [varchar] (20) NULL ,
[A_ID] [int] NULL ,
[FJ_DataAndTime] [datetime] NULL CONSTRAINT [DF__LR_FindJo__FJ_Da__3E1D39E1] DEFAULT (getdate()),
PRIMARY KEY CLUSTERED
(
[FJ_ID]
) ON [PRIMARY] ,
UNIQUE NONCLUSTERED
(
[FJ_Title]
) ON [PRIMARY] ,
CONSTRAINT [LR_FindJop_A_ID] FOREIGN KEY
(
[A_ID]
) REFERENCES [LR_Admin] (
[A_ID]
)
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
用C#程序生成出来,放入textBOX中怎么弄?