Create And Execute Table Script (SQL Server 2000)

Total article views: 446 | Views in the last 30 days: 104

 Rate this |   Join the discussion |  Briefcase

This SP will only work on SQL Server 2000 and can be placed in your master database. sp_CreateAndExecTableScript is designed to script one table and create an identical table. I designed it to use with DTS packages so that I can create an identical table with a different name, pump data into the table and then rename the new table to the name of the original table. Here is a sample call for the stored procedure to script everything related to a table: exec sp_CreateAndExecTableScript @Exec = 1, @DisplayScript = 1, @TableName = 'authors', @TableNameExt = '_New' This will create a duplicate table in the pubs database for the table authors. If you set @NoIndexes = 1 and @NoPK = 1 then you will only get the table and default values. If you set @PKOnly = 1 then you will only get the script for the primary key. If you set @NoTable = 1 then you will only get the script for the primary key and/or the indexes. If you want to see the script without executing it then make sure you set @Exec = 0 and @DisplayScript = 1. I do not guarantee it will script all tables correctly, however my testing shows it will script most tables and indexes correctly. If you find something about a table or index it doesn't script properly please let me know and I will see about fixing it.

Total article views: 446 | Views in the last 30 days: 104