question

SQLserver_novice avatar image
SQLserver_novice asked

Copy table structure adding primary key

I want to copy primary key columns of a table (only the structure) to a different table in the same database through a SQL Script. I do not want to generate the script thorugh SQL server management studio and run the script. Is this possible through "Create table" statement as i have to copy the structures of nearly 200 tables ?

Any help greatly appreciated !!!

t-sql
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Matt Whitfield avatar image
Matt Whitfield answered

Your best bet is most likely to dip into PowerShell for this. That way you can use SMO to script out the tables quickly and easily.

If it helps, this query gives you the columns from each table that are involved in the primary keys, but this is a long way off from being able to generate script reliably.

SELECT   OBJECT_NAME([i].[object_id]) AS [TableName], [c].[name]
FROM     [sys].[indexes] AS [i]
         INNER JOIN
         [sys].[index_columns] AS [ic]
         ON [ic].[index_id] = [i].[index_id]
            AND [ic].[object_id] = [i].[object_id]
         INNER JOIN
         [sys].[columns] AS [c]
         ON [c].[column_id] = [ic].[column_id]
            AND [c].[object_id] = [i].[object_id]
ORDER BY OBJECT_NAME([i].[object_id]), [ic].[key_ordinal];
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Like Matt said, either PowerShell or another .NET scripting language like IronPython or IronRuby is probably the way to go. You could also do the entire thing in SQL using Dynamic SQL to generate the Create Table statements for you.
1 Like 1 ·
Grant Fritchey avatar image
Grant Fritchey answered

I'm not sure I entirely understand the question. If I hear what I think I'm hearing, then no, CREATE TABLE is not what you want, but instead you need to use ALTER TABLE to add columns to existing structures. As Matt says, automating this through PowerShell would be the way to go.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Mark Allison avatar image
Mark Allison answered

Like the others I find your question a little vague. You could have a look at coding up some dynamic sql to produce something like

select col1,col2,col3 into mynewtable from myoldtable where 1=0

and then run it.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.