by I am involved with a database migration project and for some reason the id
fields do not have they IDENTITY property enabled and no primary keys on the
id column...
I need a stored procedure that can update all tables with an id column to
have the identity COLUMN enabled and optionally have this column as the
primary key...
Here are my failed attempts (lines that are commented because I tried to run
it from one main DB and loop through the rest (aprox. 30 databases)....
-- SQL STORED PROCEDURE NOTES
-- loop through a query to find a listing of databases
-- loop through each database pulling the table names
-- loop through the table and pull the columns....update id fields and
submitdate fields so these CF pages can stop chocking
--USE master
--IF EXISTS (SELECT name FROM sysobjects
-- WHERE name = 'update_all_id_and_dates' AND type = 'P')
-- DROP PROCEDURE update_all_id_and_dates
--GO
--CREATE PROCEDURE update_all_id_and_dates
--AS
-- Turn off the counter for affected rows
SET NOCOUNT ON
-- Declare Cursor for looping through Databases
--DECLARE db_cursor CURSOR FOR
--SELECT name FROM master..sysdatabases
--WHERE name not in ('master','model','msdb','pubs','northwind', 'tempdb',
'endorse_db')
--ORDER BY name
--OPEN db_cursor
--Loop through cursor until EOF
--BEGIN
--Declare Cursor for looping through tables
DECLARE tbl_cursor CURSOR FOR
SELECT name
FROM sysobjects
WHERE (type = 'U') AND (name NOT IN
(SELECT name
FROM sysobjects
WHERE name = 'dtproperties'))
FOR UPDATE OF id
OPEN tbl_cursor
--Loop through cursor until EOF
BEGIN
--Test to see if id column is in table, if so alter column to make it an
identity column and a primary key
IDENTITY CONSTRAINT column_id_pk PRIMARY KEY')
--Test to see if submitdate is in table, if so alter column and specifiy
a default value to currentdate
submitdate datetime DEFAULT GETDATE()')
END
-- END
-- Free table cursor memory
CLOSE tbl_cursor
DEALLOCATE tbl_cursor
-- Free database cursor memory
--CLOSE db_cursor
--DEALLOCATE db_cursor