Column names in each table must be unique
我的目的是： 比較同一個服務器上兩個不同數據庫里相同表名的兩個表（表A 和 表 B ， 其中表 B 和表 A 的結構是一樣的。但是表A有可能會增加新的列 ，如果在 A 中增加了列，我就需要先判斷在B中是否有對應的列，沒有就在B中創建相同的列），我的代碼如下：
DROP PROCEDURE SP_FOR_DB_BAKING_2
GO
CREATE PROCEDURE SP_FOR_DB_BAKING_2
@server nvarchar(100)= 'BakTest_Bak.dbo. ' ,
@tablename nvarchar(100) = 'Room_Card_Record '
AS
DECLARE @sql nvarchar(300)
DECLARE @num int
DECLARE @tablefield nvarchar(50)
DECLARE @newservertablename nvarchar(110)
DECLARE @field_type nvarchar(20)
DECLARE @null_mark smallint
DECLARE @field_length int
DECLARE @field_null nvarchar(30)
DECLARE cur_Infor_2 CURSOR FOR
SELECT [name] AS tablefield FROM syscolumns WHERE ( id = OBJECT_ID(@tablename) ) --- Get your table 's source table fields
OPEN cur_Infor_2
FETCH NEXT FROM cur_Infor_2 INTO @tablefield
WHILE @@fetch_status=0
BEGIN
SET @sql = ' select @counter = count(*) from ' + @server + 'syscolumns ' + ' where ( id = OBJECT_ID( ' + ' ' ' ' +@tablename + ' ' ' ' + ' ) ) and name = ' + ' ' ' ' + @tablefield + ' ' ' '
EXEC sp_executesql @sql , N '@counter int output ' , @num output
IF(@num = 0)
BEGIN
SELECT @tablename = a.name , @tablefield = b.name , @field_type = c.name , @field_length = b.prec , @null_mark = b.isnullable
FROM sysobjects a INNER JOIN syscolumns b ON a.id = b.id INNER JOIN systypes c ON b.xtype = c.xtype
WHERE ( a.name = @tablename ) and b.name = @tablefield and c.name <> 'sysname '
IF(@null_mark = 0 )
SET @field_null = ' NOT NULL '
ELSE
SET @field_null = ' NULL '
EXEC ( ' ALTER TABLE ' + @server +@tablename + ' ADD ' + @tablefield + ' ' + @field_type + '( ' + @field_length + ') ' + @field_null )
print 'has no field : ' + @tablefield
END
FETCH NEXT FROM cur_Infor_2 INTO @tablefield
END
CLOSE cur_Infor_2
DEALLOCATE cur_Infor_2
GO
-------- exec SP_FOR_DB_BAKING_2
---COLUMNPROPERTY
但是執行此存儲過程提示錯誤：
Server: Msg 2705, Level 16, State 4, Line 1
Column names in each table must be unique. Column name 'dateid ' in table 'BakTest_Bak.dbo.Room_Card_Record ' is specified more than once.
has no field : dateid
Server: Msg 2705, Level 16, State 4, Line 1
Column names in each table must be unique. Column name 'cardnum ' in table 'BakTest_Bak.dbo.Room_Card_Record ' is specified more than once.
has no field : cardnum
Server: Msg 2705, Level 16, State 4, Line 1
Column names in each table must be unique. Column name 'site ' in table 'BakTest_Bak.dbo.Room_Card_Record ' is specified more than once.
has no field : site
Server: Msg 2705, Level 16, State 4, Line 1
Column names in each table must be unique. Column name 'tcu ' in table 'BakTest_Bak.dbo.Room_Card_Record ' is specified more than once.
has no field : tcu
Server: Msg 2705, Level 16, State 4, Line 1
Column names in each table must be unique. Column name 'dcu ' in table 'BakTest_Bak.dbo.Room_Card_Record ' is specified more than once.
has no field : dcu
Server: Msg 2705, Level 16, State 4, Line 1
Column names in each table must be unique. Column name 'time ' in table 'BakTest_Bak.dbo.Room_Card_Record ' is specified more than once.
has no field : time
Server: Msg 2705, Level 16, State 4, Line 1
Column names in each table must be unique. Column name 'MODTIME ' in table 'BakTest_Bak.dbo.Room_Card_Record ' is specified more than once.
has no field : MODTIME
Server: Msg 2705, Level 16, State 4, Line 1
Column names in each table must be unique. Column name 'LatestTimeOfBak ' in table 'BakTest_Bak.dbo.Room_Card_Record ' is specified more than once.
has no field : LatestTimeOfBak
[解决办法]
表里面的列名不能重复,楼主的代码肯定有问题.
[解决办法]
提示信息很清楚啊，列名不能重复，SQL里面有问题
