27,582
社区成员




If object_id('TEC') is not null
Drop table TEC
Go
Create table TEC(COL nvarchar(5),CNAME nvarchar(2))
Go
Insert into TEC
select 'IMA01','名字' union all
select 'IMA02','細節'
Go
-- Test Data: TE
If object_id('TE') is not null
Drop table TE
Go
Create table TE(IMA01 nvarchar(1),IMA02 nvarchar(7))
Go
Insert into TE
select 'A','DETAILA' union all
select 'B','DETAILB'
Go
--Start
declare @s varchar(1000)
select @s=isnull(@s+';exec sp_rename ','exec sp_rename ')+quotename(OBJECT_NAME(a.id)+'.'+a.name,CHAR(39))+','
+quotename(b.CNAME,CHAR(39)) + ',''column'''
from syscolumns a join TEC b on a.id = object_id('TE') and a.name=b.COL
exec (@s)
select * from te
/*
注意: 更改对象名的任一部分都可能会破坏脚本和存储过程。
注意: 更改对象名的任一部分都可能会破坏脚本和存储过程。
名字 細節
---- -------
A DETAILA
B DETAILB
(2 行受影响)
*/
----------------------------------------------------------------
-- Author :SQL77(只为思齐老)
-- Date :2010-04-12 20:21:49
-- Version:
-- Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86)
-- Oct 14 2005 00:33:37
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:#TE
if object_id('tempdb.dbo.#TE') is not null drop table #TE
go
create table #TE([IMA01] varchar(1),[IMA02] varchar(7))
insert #TE
select 'A','DETAILA' union all
select 'B','DETAILB'
--> 测试数据:#TEC
if object_id('tempdb.dbo.#TEC') is not null drop table #TEC
go
create table #TEC([COL] varchar(5),[CNAME] varchar(4))
insert #TEC
select 'IMA01','名字' union all
select 'IMA02','細節'
--------------开始查询--------------------------
DECLARE @S VARCHAR(200)
select @S=ISNULL(@S+',','')+COL+' AS '+TE.CNAME
from #TEC TE,
(SELECT NAME,COLID FROM tempdb.dbo.SYSCOLUMNS WHERE ID=object_id('tempdb.dbo.#TE'))T
WHERE TE.COL=T.NAME ORDER BY COLID
EXEC('SELECT '+@S+' FROM #TE')
----------------结果----------------------------
/*
(2 行受影响)
(2 行受影响)
名字 細節
---- -------
A DETAILA
B DETAILB
(2 行受影响)
*/
----------------------------------------------------------------
-- Author :SQL77(只为思齐老)
-- Date :2010-04-12 20:21:49
-- Version:
-- Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86)
-- Oct 14 2005 00:33:37
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:#TE
if object_id('tempdb.dbo.#TE') is not null drop table #TE
go
create table #TE([IMA01] varchar(1),[IMA02] varchar(7))
insert #TE
select 'A','DETAILA' union all
select 'B','DETAILB'
--> 测试数据:#TEC
if object_id('tempdb.dbo.#TEC') is not null drop table #TEC
go
create table #TEC([COL] varchar(5),[CNAME] varchar(4))
insert #TEC
select 'IMA01','名字' union all
select 'IMA02','細節'
--------------开始查询--------------------------
DECLARE @S VARCHAR(20)
select @S=ISNULL(@S+',','')+COL+' AS '+TE.CNAME
from #TEC TE,
(SELECT NAME,COLID FROM tempdb.dbo.SYSCOLUMNS WHERE ID=object_id('tempdb.dbo.#TE'))T
WHERE TE.COL=T.NAME ORDER BY T.COLID
EXEC('SELECT '+@S+' FROM #TE')
----------------结果----------------------------
/*
(2 行受影响)
(2 行受影响)
名字 IMA02
---- -------
A DETAILA
B DETAILB
(2 行受影响)
*/
------------------------------------
-- Author: flystone
-- Version:V1.001
-- Date:2010-04-12 20:18:21
------------------------------------
-- Test Data: TEC
If object_id('TEC') is not null
Drop table TEC
Go
Create table TEC(COL nvarchar(5),CNAME nvarchar(2))
Go
Insert into TEC
select 'IMA01','名字' union all
select 'IMA02','細節'
Go
-- Test Data: TE
If object_id('TE') is not null
Drop table TE
Go
Create table TE(IMA01 nvarchar(1),IMA02 nvarchar(7))
Go
Insert into TE
select 'A','DETAILA' union all
select 'B','DETAILB'
Go
--Start
declare @s varchar(1000)
Select @s = isnull(@s+',','') +col+' as ['+cname+']' from syscolumns a,tec b where a.id = object_id('TE') and a.name = b.col
exec('select '+ @s + 'into tt from te;drop table te ;exec sp_rename ''tt'',''te'';')
select * from te
--Result:
/*名字 細節
---- -------
A DETAILA
B DETAILB
*/
--End
---测试数据---
if object_id('[TE]') is not null drop table [TE]
go
create table [TE]([IMA01] varchar(1),[IMA02] varchar(7))
insert [TE]
select 'A','DETAILA' union all
select 'B','DETAILB'
GO
if object_id('[TEC]') is not null drop table [TEC]
go
create table [TEC]([COL] varchar(5),[CNAME] varchar(4))
insert [TEC]
select 'IMA01','名字' union all
select 'IMA02','細節'
GO
---查询---
DECLARE @SQL VARCHAR(8000)
SELECT
@SQL=ISNULL(@SQL+',','')
+COL+' AS '+CNAME
FROM TEC
--PRINT @SQL
EXEC ('SELECT '+@SQL+' FROM TE')
---结果---
名字 細節
---- -------
A DETAILA
B DETAILB
(2 行受影响)
------------------------------------
-- Author: flystone
-- Version:V1.001
-- Date:2010-04-12 20:18:21
------------------------------------
-- Test Data: TEC
If object_id('TEC') is not null
Drop table TEC
Go
Create table TEC(COL nvarchar(5),CNAME nvarchar(2))
Go
Insert into TEC
select 'IMA01','名字' union all
select 'IMA02','細節'
Go
-- Test Data: TE
If object_id('TE') is not null
Drop table TE
Go
Create table TE(IMA01 nvarchar(1),IMA02 nvarchar(7))
Go
Insert into TE
select 'A','DETAILA' union all
select 'B','DETAILB'
Go
--Start
declare @s varchar(1000)
Select @s = isnull(@s+',','') + col+' as ['+cname+']' from syscolumns a,tec b where a.id = object_id('TE') and a.name = b.col
exec('select '+ @s + ' from te')
--Result:
/*名字 細節
---- -------
A DETAILA
B DETAILB
*/
--End