为事务发布准备主键

[yonghengdizhen]

事务发布要求所有发布表都具有主键定义,由于历史原因,可能有些表没有定义主键,但并不影响系统正常运行,发布时,这些表是无法进行事务发布的,为了支持事务发布,数据库管理员需要为这些表手工定义主键,次工作繁琐而重复,为了解决这个问题.我编写了如下脚本
SQL Server 2000下测试通过
DECLARE @SqlString AS varchar(8000)
DECLARE @TableName AS varchar(255)
DECLARE @IdentityCol AS varchar(255)
DECLARE curTableList CURSOR FOR
SELECT [NAME],(SELECT [name] FROM syscolumns WHERE [id]=sysobjects.id AND autoval IS NOT NULL) FROM sysobjects
WHERE xtype=N'U'
AND [id] NOT IN(SELECT b.id FROM sysobjects a JOIN sysobjects b ON a.xtype=N'PK' AND a.parent_obj=b.id)
AND status>0
ORDER BY NAME
OPEN curTableList
FETCH NEXT FROM curTableList INTO @TableName,@IdentityCol
WHILE @@FETCH_STATUS = 0
BEGIN
IF @IdentityCol IS NULL
/*对于没有标识列的表,为其新增标识列,并在该列上设置主键约束*/
SET @SqlString='ALTER TABLE ' + @TableName
+ ' ADD ' + @TableName + '_PK_Id'
+ ' INT IDENTITY'
+ ' CONSTRAINT ' + @TableName + '_PK PRIMARY KEY'
ELSE
/*对于已有标识列的表,在现有标识列上设置主键约束*/
SET @SqlString='ALTER TABLE ' + @TableName
+ ' ADD CONSTRAINT ' + @TableName + '_PK PRIMARY KEY(' + @IdentityCol + ')'
--Print @SqlString
EXECUTE (@SqlString)
FETCH NEXT FROM curTableList INTO @TableName,@IdentityCol
END
CLOSE curTableList
DEALLOCATE curTableList

[netcoder]

谢谢

[xxrl]

非常感谢