保存在sysproperties表中,看看有没有办法插入记录.SELECT
(case when a.colorder=1 then d.name else '' end) N'表名',
a.colorder N'字段序号',
a.name N'字段名',
(case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end) N'标识',
(case when (SELECT count(*)
FROM sysobjects
WHERE (name in
(SELECT name
FROM sysindexes
WHERE (id = a.id) AND (indid in
(SELECT indid
FROM sysindexkeys
WHERE (id = a.id) AND (colid in
(SELECT colid
FROM syscolumns
WHERE (id = a.id) AND (name = a.name))))))) AND
(xtype = 'PK'))>0 then '√' else '' end) N'主键',
b.name N'类型',
a.length N'占用字节数',
COLUMNPROPERTY(a.id,a.name,'PRECISION') as N'长度',
isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0) as N'小数位数',
(case when a.isnullable=1 then '√'else '' end) N'允许空',
isnull(e.text,'') N'默认值',
isnull(g.[value],'') AS N'字段说明'
--into ##txFROM 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'
left join syscomments e
on a.cdefault=e.id
left join sysproperties g
on a.id=g.id AND a.colid = g.smallid
order by object_name(a.id),a.colorder
(case when a.colorder=1 then d.name else '' end) N'表名',
a.colorder N'字段序号',
a.name N'字段名',
(case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end) N'标识',
(case when (SELECT count(*)
FROM sysobjects
WHERE (name in
(SELECT name
FROM sysindexes
WHERE (id = a.id) AND (indid in
(SELECT indid
FROM sysindexkeys
WHERE (id = a.id) AND (colid in
(SELECT colid
FROM syscolumns
WHERE (id = a.id) AND (name = a.name))))))) AND
(xtype = 'PK'))>0 then '√' else '' end) N'主键',
b.name N'类型',
a.length N'占用字节数',
COLUMNPROPERTY(a.id,a.name,'PRECISION') as N'长度',
isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0) as N'小数位数',
(case when a.isnullable=1 then '√'else '' end) N'允许空',
isnull(e.text,'') N'默认值',
isnull(g.[value],'') AS N'字段说明'
--into ##txFROM 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'
left join syscomments e
on a.cdefault=e.id
left join sysproperties g
on a.id=g.id AND a.colid = g.smallid
order by object_name(a.id),a.colorder
CREATE TABLE T_1 "用户表"
(
COL_5 "这些" SMALLINT,
COL_6 "描述" SMALLINT NOT NULL,
COL_7 "怎么写" SMALLINT NOT NULL
)
我的意思有没有办法在create table语句中写入?有没有这个语法?
sql server中倒出的表的脚本中有这些东东!不过要在导的过程中把扩展属性打钩.在T1表中,列名A 描述设为"aaaaaaa"exec sp_addextendedproperty N'MS_Description', N'aaaaaaa', N'user', N'dbo', N'table', N't1', N'column', N'a'
RECONFIGURE WITH OVERRIDE--更新
update sysproperties
set value = cast(value as nvarchar) + N' -> ' + syscolumns.name
from syscolumns
where sysproperties.id =syscolumns.id and sysproperties.smallid = syscolumns.colid
--插入
insert into sysproperties (id,smallid,type,name,value)
select id,colid,4,N'MS_Description', name
FROM syscolumns
where id in (select id
from sysobjects
where xtype='U'
and name <> 'dtproperties'
)
and xtype in (select xusertype
from systypes
)
and not EXISTS (SELECT *
FROM sysproperties
WHERE id <> syscolumns.id
AND smallid <> syscolumns.id
)--更新
update sysproperties
set value = N'[' + cast(value as nvarchar) + N']'
go
sp_configure 'allow updates',0
RECONFIGURE WITH OVERRIDE
--delete from sysproperties
RECONFIGURE WITH OVERRIDE--更新
update sysproperties
set value = cast(value as nvarchar) + N' -> ' + syscolumns.name
from syscolumns
where sysproperties.id =syscolumns.id and sysproperties.smallid = syscolumns.colid
and sysproperties.name = N'MS_Description'--插入
insert into sysproperties (id,smallid,type,name,value)
select id,colid,4,N'MS_Description', name
FROM syscolumns
where id in (select id
from sysobjects
where xtype='U'
and name <> 'dtproperties'
)
and xtype in (select xusertype
from systypes
)
and not EXISTS (SELECT *
FROM sysproperties
WHERE id <> syscolumns.id
AND smallid <> syscolumns.id
)--更新
update sysproperties
set value = N'[' + cast(value as nvarchar) + N']'
go
sp_configure 'allow updates',0
RECONFIGURE WITH OVERRIDE
[id] [int] IDENTITY (1, 1) NOT NULL ,
[epid] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[epname] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[epold] [int] NULL ,
[epwage] [money] NULL ,
[epfatherid] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[level] [int] NULL
) ON [PRIMARY]
GO
exec sp_addextendedproperty N'MS_Description', N'hahahahaha', N'user', N'dbo', N'table', N'tree', N'column', N'epid'
GO
declare @ varchar(8000)
set @='insert into sysproperties (id,smallid,type,name,value) ' + char(13)
+ 'select sysobjects.id,syscolumns.colid,4,N''MS_Description'',T.F3 ' + char(13)
+ 'from' + char(13) + '('
declare @Flag int
set @Flag = 0
--减少数据量
SET ROWCOUNT 9
select @ = @ + 'select ''[' + sysobjects.name + ']'''
+ case when @Flag = 0 then ' AS F1 ' else '' end
+ ',''[' + syscolumns.name + ']'''
+ case when @Flag = 0 then ' AS F2 ' else '' end
+ ',N'''
+ cast(sysproperties.value as varchar)
+ '''' + case when @Flag = 0 then ' AS F3 ' else '' end
+ char(13)
+ 'union'
+ char(13)
,@Flag = 1
from sysproperties
left join sysobjects on sysproperties.id = sysobjects.id
left join syscolumns on sysproperties.id = syscolumns.id and sysproperties.smallid = syscolumns.colid
where sysproperties.type = 4
and sysproperties.name = 'MS_Description'
and sysproperties.id in (select id
from sysobjects
where xtype='U' and name <> 'dtproperties')set @=left(@,len(@)-len('union')-1) + ') T ' + char(13)
+ 'left join sysobjects on T.F1 = ''['' + sysobjects.name + '']''' + char(13)
+ 'left join syscolumns on syscolumns.id = sysobjects.id and T.F2 = ''['' + syscolumns.name + '']''' + char(13)
+ 'where not exists (select *
from sysproperties
where id = syscolumns.id
and smallid = syscolumns.colid
and name = ''MS_Description''
)
and
sysobjects.id in (select id
from sysobjects
where xtype=''U'' and name <> ''dtproperties'')'print @
--动态 SQL 已经生成
--====================
--测试:
use northwindcs
exec sp_configure 'allow updates',1
RECONFIGURE WITH OVERRIDE
exec(@)
exec sp_configure 'allow updates',1
RECONFIGURE WITH OVERRIDEselect *
from sysproperties
where name = 'MS_Description'