我的目的是： 比較同一個服務器上兩個不同數據庫里相同表名的兩個表（表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( ' + ' ' ' ' [email protected] + ' ' ' ' + ' ) ) 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