Add PK to all tables in the database

Hard to import data into the database, but found that forget to create a script to forget PK.

Fortunately, the table has a regular, each table has an [ID] int field, PK is built on it.

Note:

1 for the readability of the code, not using the ' SQL splicing ' method, but the use of ' first placeholder, after replacement ' method-see the inside of the replace statement. Recommend to everyone

2 because it is executing the DDL, for careful consideration, ' generate ' rather than ' direct execute ' SQL. If not, the reader can take the EXEC statement inside to remove the comment.

----This is the code---------------------------------------

Set ANSI_NULLS on Go SET quoted_identifier in Go--=============================================--Author:  
     
Yew--Create date:2011-06-12-Description: Add PK (on ID)/*---------History for all tables---------------------------- ---------------------------------------------* *-============================================= CREATE Procedur E [TOOL]. 
[SP_DB_ADDPK] 
     
    As BEGIN SET NOCOUNT on; DECLARE @vTable varchar (MB), @vSchema varchar (m), @vSql varchar (max) DECLARE Cur_ For SELECT [name], schema_name (schema_id) from sys.objects WHERE type in (N ' U ') Order B Y schema_id, name OPEN cur_ FETCH NEXT from Cur_ to @vTable, @vSchema while @ @FETCH_STATUS = 0 BEGIN SET @vSQL = N ' ALTER TABLE [#Schema #]. [#Table #] ADD CONSTRAINT [pk_#table#] PRIMARY KEY CLUSTERED ([ID] ASC) with (sort_in_tempdb = off, Ignore_dup_key = on, on line = off)On [PRIMARY] go ' Set @vSQL = replace (@vSQL, ' #Table # ', @vTable) Set @vSQL = replace (@vSQL, ' #Sch
    ema# ', @vSchema) print @vSql--EXEC (@vSql) FETCH NEXT from Cur_ to @vTable, @vSchema end Close Cur_ deallocate cur_ End