Menu
I would like to get the details of all indexes, keys, and foreign keys from a database in SQL Server (2008). How do I do this?
I plan to use this to synchronize those properties across a couple of somewhat similar databases.
I can use SQL Server Management Studio, but I cannot do a full backup of a database because of restrictions set by the web hoster.
--
Secondary question that you do not need to answer:
Why can't there be something similar to the database schema in Mysql that simply lists all of the database structure in text SQL script format?
582
When you define a foreign key constraint in your database table, an index will not be created automatically on the foreign key columns, as in the PRIMARY KEY constraint situation in which a clustered index will be created automatically when defining it.
MySQL requires that foreign key columns be indexed; if you create a table with a foreign key constraint but no index on a given column, an index is created. Information about foreign keys on InnoDB tables can also be found in the INNODB_FOREIGN and INNODB_FOREIGN_COLS tables, in the INFORMATION_SCHEMA database.
Right-click on the database and go to Tasks, Generate Scripts.... This will bring up a script generation wizard that will generate DROP and CREATE scripts for whatever schema constructs that you select. Save this answer.
Assuming you are using atleast SQL Server 2005 or above, you can use the Database Publishing Wizard to script your schema
This can be used to generate scripts for schema only, data or both.
It integrates directly into Visual Studio 2005 and/or Visual Web Developer 2005
If you are using VS2008, v1.2 version of SQL Publishing Wizard comes pre-installed. You can check out here where to invoke it from.
199
If you are looking for granular, more specific objects to script you can also use generate scripts from the respective DB's Task > Generate Scripts option.
Check http://www.kodyaz.com/articles/sql-server-script-data-with-generate-script-wizard.aspx for details.
44
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
