具体方法如下

我要查找值为‘WSCOL1525’的字段。

需求:把一个表某个字段内容复制到另一张表的某个字段。

1. 增加字段说明

一:update 表2 set (要插入的列名)= select 表1.某一列 from 表1 left
jion 表2 on 表1和表2的关联 where …..  

declare @cloumns varchar(40)
declare @tablename varchar(40)
declare @str varchar(40)
declare @counts int
declare @sql nvarchar(2000)
declare MyCursor Cursor For
Select a.name as Columns, b.name as TableName from syscolumns
a,sysobjects b,systypes c
where a.id = b.id
and b.type = ‘U’
and a.xtype=c.xtype
and c.name like ‘%char%’
set @str=’

实现sql语句1:

EXEC sp_addextendedproperty 
    ‘MS_Description’, 
    ‘some description’, 
    ‘user’, 
     dbo, 
    ‘table’, 
澳门皇冠金沙网站,     table_name, 
    ‘column’, 
     column_name

二:update 表1 set 表1.列=表2.列 from 表2 where 表2.id=表1.id 

declare @cloumns varchar(40)
declare @tablename varchar(40)
declare @str varchar(40)
declare @counts int
declare @sql nvarchar(2000)
declare MyCursor Cursor For
Select a.name as Columns, b.name as TableName from syscolumns
a,sysobjects b,systypes c
where a.id = b.id
and b.type = ‘U’
and a.xtype=c.xtype
and c.name like ‘%char%’
set @str=’WSCOL1525′
Open MyCursor
Fetch next From MyCursor Into @cloumns,@tablename
While(@@Fetch_Status = 0)
Begin
set @sql=’select @tmp_counts=count(*) from ‘ +@tablename+ ‘ where ‘
+@cloumns+’ = ”’ +@str+ ””
execute sp_executesql @sql,N’@tmp_counts int out’,@counts out
if @counts>0
begin
print ‘表名为:’+@tablename+’,字段名为’+@cloumns
end
Fetch next From MyCursor Into @cloumns,@tablename
End
Close MyCursor
Deallocate MyCursor

复制代码 代码如下:

 

三:update _a set 列=_b.列 from 表1 _a join 表2 _b on _a.id=_b.id


Open MyCursor
Fetch next From MyCursor Into @cloumns,@tablename
While(@@Fetch_Status = 0)
Begin
set @sql=’select @tmp_counts=count(*) from ‘ +@tablename+ ‘ where ‘
+@cloumns+’ = ”’ +@str+ ””
execute sp_executesql @sql,N’@tmp_counts int out’,@counts out
if @counts>0
begin
print ‘表名为:’+@tablename+’,字段名为’+@cloumns
end
Fetch next From MyCursor Into @cloumns,@tablename
End
Close MyCursor
Deallocate MyCursor

UPDATE file_manager_folder f1
LEFT OUTER JOIN file_manager_folder f2
    ON f1.name = f2.name AND f2.parentId = 54
SET f1.parentId = 54
WHERE f2.name IS NULL AND f1.id IN (1,2,3);

  • Some Description , 是要增加的说明内容
  • table_name, 是表名
  • column_name , 是字段名

结果:

实现sql语句2:

 

澳门皇冠金沙网站 1

复制代码 代码如下:

2. 增加表的说明

 

update B set extra = A.extra from A join B on (A.id = B.id);

EXEC sp_addextendedproperty 
    ‘MS_Description’, 
    ‘some description’, 
    ‘user’, 
     dbo, 
    ‘table’, 
     table_name

实现sql语句3:

 

复制代码 代码如下:

参数说明同上

update b set b.sms = (select a.sms from a where a.id = b.id)

 

需要确定两张表中的id都是主键或者唯一

3. 取得字段说明内容

**实现sql语句4:

  

**

SQL Server 2000
SQL Server 2005 ( 包括 express)
SELECT 
    [Table Name] = i_s.TABLE_NAME, 
    [Column Name] = i_s.COLUMN_NAME, 
    [Description] = s.value 
FROM 
    INFORMATION_SCHEMA.COLUMNS i_s 
LEFT OUTER JOIN 
    sysproperties s 
ON 
    s.id = OBJECT_ID(i_s.TABLE_SCHEMA+’.’+i_s.TABLE_NAME) 
    AND s.smallid = i_s.ORDINAL_POSITION 
    AND s.name = ‘MS_Description’ 
WHERE 
    OBJECTPROPERTY(OBJECT_ID(i_s.TABLE_SCHEMA+’.’+i_s.TABLE_NAME), ‘IsMsShipped’)=0 
    — AND i_s.TABLE_NAME = ‘table_name’ 
ORDER BY 
    i_s.TABLE_NAME, i_s.ORDINAL_POSITION
SELECT 
    [Table Name] = OBJECT_NAME(c.object_id), 
    [Column Name] = c.name, 
    [Description] = ex.value 
FROM 
    sys.columns c 
LEFT OUTER JOIN 
    sys.extended_properties ex 
ON 
    ex.major_id = c.object_id 
    AND ex.minor_id = c.column_id 
    AND ex.name = ‘MS_Description’ 
WHERE 
    OBJECTPROPERTY(c.object_id, ‘IsMsShipped’)=0 
    — AND OBJECT_NAME(c.object_id) = ‘your_table’ 
ORDER 
    BY OBJECT_NAME(c.object_id), c.column_id

复制代码 代码如下:

 

UPDATE A SET A.SMS = (SELECT B.SMS FROM B WHERE A.ID = B.ID) WHERE
EXISTS (SELECT 1 FROM B WHERE A.ID = B.ID);

4. 取得表说明

实现sql语句5: 复制一个表字段数据到另外一个表的字段,可以这么写:
实现sql语句5:

  

复制代码 代码如下:

SELECT 表名 = case when a.colorder = 1 then d.name 
                   else ” end, 
       表说明 = case when a.colorder = 1 then isnull(f.value, ”) 
                     else ” end
 
FROM syscolumns a 
       inner join sysobjects d 
          on a.id = d.id 
             and d.xtype = ‘U’ 
             and d.name <> ‘sys.extended_properties’
       left join sys.extended_properties   f 
         on a.id = f.major_id 
            and f.minor_id = 0
Where (case when a.colorder = 1 then d.name else ” end) <>”

UPDATE tb_1 INNER JOIN tb_2 ON tb_1.tid = tb_2.tid
SET tb_1.tcontent = tb_2.tcontent

 

附:同表复制
**需求:把同一张表的一个字段内的内容复制到另一个字段里

 

**例1:
我想把article表中A字段的内容复制到article表中B字段里面sql语句为:

 

复制代码 代码如下:

SELECT

update article set B=A;

        (case when a.colorder=1 then d.name else ” end) 表名,

例2:
有时候,我们需要复制某个字段一整列的数据到另外一个新的字段中,这很简单,SQL可以这么写:

        a.colorder 字段序号,

复制代码 代码如下:

        a.name 字段名,

UPDATE tb_1 SET content_target = content_source;

       g.[value] AS 字段说明

大概写法如下:

 

复制代码 代码如下:

FROM syscolumns a left join systypes b

Update {your_table} set {source_field} = {object_field} WHERE cause

on a.xtype=b.xusertype

实现sql语句1: 复制代码 代码如下: UPDATE file_manager_folder f1 LEFT
OUTER JOIN file_manag…

inner join sysobjects d

on a.id=d.id and d.xtype=’U’ and d.name<>’dtproperties’

left join sys.extended_properties g

on a.id=g.major_id AND a.colid = g.minor_id

WHERE d.[name] <>’table_desc’
–你要查看的表名,注释掉,查看当前数据库所有表的字段信息

order by a.id,a.colorder

 

--创建表及描述信息

create   table   表(a1   varchar(10),a2   char(2))

--为表添加描述信息
EXECUTE   sp_addextendedproperty   N'MS_Description',   '人员信息表',   N'user',   N'dbo',   N'table',   N'表',   NULL,   NULL

--为字段a1添加描述信息
EXECUTE   sp_addextendedproperty   N'MS_Description',   '姓名',   N'user',   N'dbo',   N'table',   N'表',   N'column',   N'a1'

--为字段a2添加描述信息
EXECUTE   sp_addextendedproperty   N'MS_Description',   '性别',   N'user',   N'dbo',   N'table',   N'表',   N'column',   N'a2'
--更新表中列a1的描述属性:
EXEC   sp_updateextendedproperty   'MS_Description','字段1','user',dbo,'table','表','column',a1

--删除表中列a1的描述属性:
EXEC   sp_dropextendedproperty   'MS_Description','user',dbo,'table','表','column',a1

--删除测试
drop   table   表 

发表评论

电子邮件地址不会被公开。 必填项已用*标注

网站地图xml地图