Add PK w/sp_msforeachtable - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Add PK w/sp_msforeachtable

Author

Topic

Ken Blum
Constraint Violating Yak Guru

383 Posts

Posted - 2004-08-03 : 13:15:12

How can I use sp_msforeachtable to add an identity column with a primary key constraint?

EXEC SP_MSFOREACHTABLE @command1 = "ALTER TABLE ? ADD SQL_ID INT IDENTITY CONSTRAINT SQL_ID_PK PRIMARY KEY "

The above fails on the second table because "SQL_ID_PK" is created for the first table.

TIA

X002548
Not Just a Number

15586 Posts

Posted - 2004-08-03 : 14:06:44

How about...



USE Northwind
GO

CREATE TABLE myTable99(Col1 int)
GO

sp_help myTable99
GO

DECLARE myCursor99 CURSOR 
FOR
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.Tables 
-- So I don't hose everything
WHERE TABLE_NAME = 'myTable99'

DECLARE @TABLE_NAME sysname, @SQL varchar(8000)

OPEN myCursor99

FETCH NEXT FROM myCursor99 INTO @TABLE_NAME

WHILE @@FETCH_STATUS = 0
  BEGIN
	SELECT @SQL = 'ALTER TABLE ' 
	+ @TABLE_NAME 
	+ ' ADD SQL_ID INT IDENTITY(1,1) CONSTRAINT ' 
	+ @TABLE_NAME 
	+ '_SQL_ID_PK PRIMARY KEY'

	EXEC(@SQL)
	
	FETCH NEXT FROM myCursor99 INTO @TABLE_NAME
  END
 	
CLOSE myCursor99
DEALLOCATE myCursor99
GO

sp_help myTable99
GO

DROP TABLE myTable99
GO

Brett

8-)

Ken Blum
Constraint Violating Yak Guru

383 Posts

Posted - 2004-08-03 : 14:20:29

Yup, I imagine that's one way. Is there a function that returns just the table name of an object? Maybe I could use something like that in MSFOREACHTABLE...

Ken Blum
Constraint Violating Yak Guru

383 Posts

Posted - 2004-08-03 : 14:27:01

Duh - like Object_Name()

Ken Blum
Constraint Violating Yak Guru

383 Posts

Posted - 2004-08-03 : 15:15:54

Never mind. Didn't realize I could eliminate the constraint name..

SP_MSFOREACHTABLE @command1 = "ALTER TABLE ? ADD SQL_ID INT IDENTITY PRIMARY KEY"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources