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 !!!
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];
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.
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.
