Create sister table without constraints
-
Hi All,
I have a task of creating sister tables for entire DB and, since there are more than 1,000 tables, I don't want to do it manually.
The problem is that I have to create new tables with absolutely no constarints (no PK or FK, no indexes, no Identities).
This prevents me from using simple statement
select * into new_table from old_table where 1=2, because this statement creates new table with identity property for old table's PK.
For each table i can get everything I need to create a new table by using
select column_name,* from information_schema.columns
where table_name = 'old_table'
order by ordinal_position
My question is how to create table in Transact SQL using the info from above,
or is there any better way to do it.
Thanks for your help
-
i think the easiest way is going to be scripting the tables out, and doing two passes of find and replace to the script.
you can turn off almost all the scripting options for constraints, foreign keys, etc in SSMS.
script all the tables out, then find/replace IDENTITY(1,1) with a blank, and "CREATE TABLE" with "CREATE TABLE OLD_"
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Thanks Lowell.
That's not exactly what i'm looking for, bit it's definitely an option to consider.
-
I use a method here which taps in to Phil Factor's sp_scriptfor stored procedure.
Using this stored procedure we can populate the table #HOLD_Scripts with all the scripts we require. Only we need to adapt them to firstly generate global temporary tables and then to replace the Identity types with blanks. Now that we have the global temporary tables, we can use these to generate your NEW tables but without Identity types etc. I have left the last 2 statements as PRINTS so you can review them prior to execution
CREATE TABLE #HOLD_Scripts (Build_Script VARCHAR(MAX), Table_Name NVARCHAR(200))DECLARE @SQL VARCHAR(MAX)
SELECT @SQL = (SELECT 'INSERT #HOLD_Scripts (Build_Script) EXEC sp_scriptfor ''' + QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) + '''' + CHAR(10)
FROM information_schema.tables WHERE TABLE_TYPE = 'BASE TABLE' FOR XML PATH(''), TYPE).value('.[1]', 'varchar(MAX)')
EXEC(@SQL)
UPDATE #HOLD_Scripts
SET Build_Script = REPLACE(Build_Script, '].[', '].[##')
UPDATE #HOLD_Scripts
SET Build_Script = STUFF(Build_Script, CHARINDEX('IDENTITY (', Build_Script), CHARINDEX(')', Build_Script, CHARINDEX('IDENTITY (', Build_Script) + 11) - CHARINDEX('IDENTITY (', Build_Script) + 1, '')
WHERE CHARINDEX('IDENTITY (', Build_Script) > 0
UPDATE #HOLD_Scripts
SET Table_Name = SUBSTRING(Build_Script, CHARINDEX('CREATE TABLE [', Build_Script) + 13, CHARINDEX(']', Build_Script, CHARINDEX('].[', Build_Script) + 1) - CHARINDEX('CREATE TABLE [', Build_Script) - 12)
GO
DECLARE @SQL VARCHAR(MAX)
SELECT @SQL = (SELECT Build_Script + CHAR(10) FROM #HOLD_Scripts FOR XML PATH(''), TYPE).value('.[1]', 'varchar(MAX)')
PRINT(@SQL)
--EXEC(@SQL)
GO --This statement generates the global temporary tables
DECLARE @SQL VARCHAR(MAX)
SELECT @SQL = (SELECT 'SELECT * INTO ' + REPLACE(Table_Name, '].[##', '].[NEW_') + ' FROM ' + Table_Name + CHAR(10)
FROM #HOLD_Scripts FOR XML PATH(''), TYPE).value('.[1]', 'varchar(MAX)')
PRINT(@SQL)
--EXEC(@SQL)
GO --This statement generates all the NEW tables from the global temporary tables
-
Great.
Thanks a lot.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply