Dynamically Alter all your Tables.....

,

This script allows you to dynamically alter all the tables in your database.  In the script i am building up an alter statement that adds a 'TimeStamp' column a table.

If anyone knows a better way to do this kind of thing, feel free to drop me a mail (jamie.moffat@infographics.co.uk)

--ALTER TABLES SCRIPT
--change the line marked *** to suit your update needs....
-- Jamie Moffat, 4/2/02

DECLARE tableNamesCursor CURSOR
FOR
	--***               
   	select 'ALTER TABLE ['+ o.name+ '] ADD TimeStamp smalldatetime NOT NULL
		CONSTRAINT AddDateDflt'+ cast(o.id as varchar(10))+ ' DEFAULT getdate() WITH VALUES'

	--  removes the TimeStamp column, and constraint from all tables
	--select 'ALTER TABLE ['+ o.name+ '] DROP CONSTRAINT AddDateDflt'+ cast(o.id as varchar(10)) + '
	--	ALTER TABLE ['+ o.name+ '] DROP COLUMN TimeStamp' 
	from sysobjects o, sysindexes i
	where i.id = o.id
	and indid in(0,1)
	and o.name not like 'sys%'   
	and o.name not like 'dt%'
	order by o.name
OPEN tableNamesCursor
DECLARE @alterTableSql nvarchar(400)
FETCH NEXT FROM tableNamesCursor INTO @alterTableSql
WHILE (@@FETCH_STATUS <> -1)
BEGIN
   IF (@@FETCH_STATUS <> -2)
   BEGIN   
	print @alterTableSql
      	exec sp_executesql @alterTableSql
   END
   FETCH NEXT FROM tableNamesCursor INTO @alterTableSql
END
CLOSE tableNamesCursor
DEALLOCATE tableNamesCursor

Rate

Share

Share

Rate