SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 How to script Keys
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

rama108
Posting Yak Master

109 Posts

Posted - 11/01/2013 :  15:43:45  Show Profile  Reply with Quote
Is there a way to script all keys from a table?

Thanks.

James K
Flowing Fount of Yak Knowledge

3649 Posts

Posted - 11/01/2013 :  16:05:45  Show Profile  Reply with Quote
Does this give you what you are looking for?
SELECT  c.NAME AS columnName, i.*
FROM    sys.indexes i
		INNER JOIN sys.COLUMNS c ON c.OBJECT_ID = i.OBJECT_ID
WHERE
	OBJECT_NAME(c.object_id) = 'YourTableName'
If you are trying to generate the scripts to create the keys/indexes, right click on the database name, Tasks-> Generate Scripts. There is an option that allows you to script indexes (along with the script for the table)
Go to Top of Page

rama108
Posting Yak Master

109 Posts

Posted - 11/01/2013 :  20:38:43  Show Profile  Reply with Quote
James,
Thank you for your response. May be you can help me with this. I have too many tables in the database. When I use the Import data wizard to import the data and the table structure in another database, it will not import the keys, column default values and the identity. I select Enable Identity, but it is not for actually importing the column as identity. So how do i use the wizard to import keys, indexes, identity and column defaults.

Thank you.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 11/02/2013 :  13:16:34  Show Profile  Reply with Quote
quote:
Originally posted by rama108

James,
Thank you for your response. May be you can help me with this. I have too many tables in the database. When I use the Import data wizard to import the data and the table structure in another database, it will not import the keys, column default values and the identity. I select Enable Identity, but it is not for actually importing the column as identity. So how do i use the wizard to import keys, indexes, identity and column defaults.

Thank you.


you cant use wizard for that. you need to script and apply them separately.


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 11/02/2013 :  13:45:09  Show Profile  Reply with Quote
If you've to do this in SSIS, you've task called Transfer SQL Server Objects which can also be used.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

rama108
Posting Yak Master

109 Posts

Posted - 11/02/2013 :  16:02:07  Show Profile  Reply with Quote
Thanks.
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3649 Posts

Posted - 11/02/2013 :  16:55:50  Show Profile  Reply with Quote
quote:
Originally posted by rama108

James,
Thank you for your response. May be you can help me with this. I have too many tables in the database. When I use the Import data wizard to import the data and the table structure in another database, it will not import the keys, column default values and the identity. I select Enable Identity, but it is not for actually importing the column as identity. So how do i use the wizard to import keys, indexes, identity and column defaults.

Thank you.

use the second method I described. That is to say, use "generate scripts" to script all your tables with keys, constraints; apply them to your target database. Then use import/export wizard.
Go to Top of Page

rama108
Posting Yak Master

109 Posts

Posted - 11/03/2013 :  11:10:40  Show Profile  Reply with Quote
Thanks James.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000