use admin GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[makePKeyScript]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[makePKeyScript] GO create procedure dbo.makePKeyScript @dbName varchar(30) as /******************************************************************************************************* * admin.dbo.makePKeyScript * Creator: Bill Wunder * * Date: 3-13-2003 * * Project: utility * * Description: make a script to retrofit an identity primary key (rid) to all tables in * in a database with no primary key. (Replication needs primary keys) * * Usage: EXECUTE admin.dbo.makePKeyScript * notes: * * Modifications: * Developer Name Date Brief Description * ------------------ -------- ------------------------------------------------------------ * ********************************************************************************************************/ set nocount on -- if no pkey make script to add one declare @sql nvarchar(2000) set @sql = 'if not exists (select 1 from sysindexes where status&2048=2048 and id = object_id(''''?'''')) print ''''alter table ? add rid int identity constraint pk_'''' + replace(replace(''''?'''',''''[dbo].['''',''''''''),'''']'''','''''''') + ''''__rid primary key''''' set @sql = 'exec sp_msforeachtable '+ '''' + @sql + '''' set @sql = 'use ' + @dbName + ' ' + @sql print 'use ' + @dbName exec sp_executesql @sql