January 15, 2009 at 2:27 am
Hi there,
I want to create a stored procedure, which can "copy" the primary keys from one table to another. (SQL 2000).
But I dont know how!
Notice that the tablename is dynamic and so is the database.
Can anyone help.
Kind regards,
Anders Florentin
January 15, 2009 at 7:59 am
You need to specify more information about what you need, the schema of the table, the setup of the environment if you want specific help. The answer will vary depending on indentity columns, multiple servers, etc.
Without any other information if the table name needs to be dynamic then you will need to use dynamic sql.
January 15, 2009 at 8:03 am
Do you want to copy primary key values to another table? What is the schema of the destination table?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
January 16, 2009 at 1:00 am
can you elaborate it more what operation you want to perform primary key on schema / data?
Abhijit - http://abhijitmore.wordpress.com
January 21, 2009 at 2:03 am
Plz give the complete & correct details of your requirement.
Not sure what you are expecting by saying "Copy primary key from one table to another"...
Cheers,
Suresh
Suresh
January 21, 2009 at 7:37 am
Hi,
Actually I just wanted to make a copy of an entire table and its design...
Kind regards,
Anders
January 22, 2009 at 5:47 am
Hi,
Right Click on the Designated Table and Choose All Tasks --> Generate Script.
1) If you wish to know which table depends on which table, to get the the right primary table, Choose View Dependencies.
2) Now you in the Dialog, Click on the Options Tab. There Check the Script Primary Keys and Foreign Keys option.
You are now good to go ;)!
Cheers!
Anil Mahadev
Senior SQL Server DBA
MISPL ,
Bangalore
INDIA
January 22, 2009 at 5:58 am
agh100 (1/21/2009)
Hi,Actually I just wanted to make a copy of an entire table and its design...
Kind regards,
Anders
This is a quick and dirty way for the columns : SELECT * INTO temp FROM db.TableName
I would not use that in production tho.
January 23, 2009 at 1:57 am
I know, but by doing that you do not get keys and indexes... 😉
January 23, 2009 at 2:57 am
Below queries may be useful to you. You can generate CREATE statment for table structure using these queries.
SELECT * INTO FROM db.TableName
SELECT A.name, B.name, D.name, B.type_desc FROM sys.key_constraints A
INNER JOIN sys.indexes B ON B.object_id = A.parent_object_id AND B.index_id = A.unique_index_id
INNER JOIN sys.index_columns C ON C.index_id = B.index_id AND C.object_id = B.object_id
INNER JOIN sys.columns D ON D.column_id = C.column_id AND D.object_id = B.object_id
WHERE OBJECT_NAME(A.parent_object_id) = 'yourTableName' AND A.type = 'PK'
SELECT B.name, D.name, B.type_desc FROM sys.indexes B
INNER JOIN sys.index_columns C ON C.index_id = B.index_id AND C.object_id = B.object_id
INNER JOIN sys.columns D ON D.column_id = C.column_id AND D.object_id = B.object_id
WHERE OBJECT_NAME(B.object_id) = 'yourTableName' AND B.index_id>2
Regards,
Nitin
January 23, 2009 at 2:59 am
you can refer this link for further referenc
http://www.codeproject.com/KB/database/SQL_DB_DOCUMENTATION.aspx
Regards,
Nitin
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply