How to script indexes, keys, foreign keys in SQL Server

Question

I would like to get information about all indexes, keys, and foreign keys from a database in SQL Server (2008). How to do it?

I plan to use this to synchronize these properties in several somewhat similar databases.

I can use SQL Server Management Studio, but I can not make a full backup of the database due to restrictions set by the web host.

-

A secondary question that you do not need to answer:

Why can't there be something similar to a database schema in Mysql that simply lists the entire database structure in SQL text formatted script?

Answer 1

Assuming you are using atleast SQL Server 2005 or higher, you can use the Database Publishing Wizard before your schema script

This can be used to create scripts only for a schema, data, or both.

It integrates directly with Visual Studio 2005 and / or Visual Web Developer 2005

If you are using VS2008, SQL Server W1.2 comes with a preinstalled version. You can check here where to call it.

Answer 2

If you are looking for detailed, more specific objects for a script, you can also use the generation scripts from the corresponding DB Task > Generate Scripts option.

Check out http://www.kodyaz.com/articles/sql-server-script-data-with-generate-script-wizard.aspx for details.

Answer 3

How to find foreign key dependencies in SQL Server?

Answer 4

If you need to get a script from T-SQL, then only using xp_cmdshell. For example, the concreate scripting index to represent Concreate with SMO and powershell (the result is in the @ script variable, you can execute it with sp_executesql):

 DECLARE @OUTPUT TABLE (line nvarchar(max)) DECLARE @cmd VARCHAR(8000), @ps VARCHAR(8000), @psLoadAssemblies VARCHAR(8000), @script nvarchar(max) ='' DECLARE @srv nvarchar(max)='<server name>', @ln nvarchar(max)='<login>', @pw nvarchar(max)='<password>', @db nvarchar(max) = '<database>', @schemaName nvarchar(max) = '<schema>', -- without '[' ']' @viewName nvarchar(max) = '<view name>', -- without '[' ']' @indexName nvarchar(max) = '<index name>' -- without '[' ']' SET @psLoadAssemblies = '[System.Reflection.Assembly]::LoadWithPartialName(''Microsoft.SqlServer.SMO'')|Out-Null;' SET @ps='$using=''Microsoft.SqlServer.Management.Smo'';$s=new-object($using+''.Server'') $srv;$c = $s.ConnectionContext;$c.LoginSecure=$false;$c.Login=$ln;$c.Password=$pw; Write-Host ($s.Databases[$db].Views.Item($viewName,$schemaName).Indexes[$indexName].Script())' SET @ps=REPLACE(@ps,'$srv','''' +@srv +'''') SET @ps=REPLACE(@ps,'$ln','''' +@ln +'''') SET @ps=REPLACE(@ps,'$pw','''' +@pw +'''') SET @ps=REPLACE(@ps,'$db','''' +@db +'''') SET @ps=REPLACE(@ps,'$schemaName','''' +@schemaName +'''') SET @ps=REPLACE(@ps,'$viewName','''' +@viewName +'''') SET @ps=REPLACE(@ps,'$indexName','''' +@indexName +'''') SET @cmd = 'powershell -Command "' +@psLoadAssemblies +@ps +'"' exec dev.Msg @cmd INSERT INTO @OUTPUT exec xp_cmdshell @cmd SELECT @script+line FROM @OUTPUT WHERE line is not null PRINT @script 

PS For those who ask why we need such tricks: in some scenarios, for example. “data import using a third-party tool”, the drop-recreate approach works better than enable-disable objects, for example. because such a third-party tool can cause “crop”, and if your table participates in a schema-bound view, you will get a third-party tool error (a truncating table participating in indexed views throws an error, so we are forced to discard the view with all indices before importing and recreate it after).

Answer 5

As an alternative to InSane's perfect answer, you can click any object in SSMS before the script in a text file or in a window.
Several free and nonfree products also allow you, including WinSQL .

Answer 6

I saw a comment or response from the user "dontomoso". After the translation into English, it seems that “The operation is invalid due to the current state of the object. (SqlPubWiz)” in the “Database Publishing Wizard” - this is an error.

After so many experiments and tests in this application. The database name is case sensitive. Set the correct value for the -d option. The solution is simple, change the default schema name or the start directory name to the same as "used when creating the database." For example. When creating, if using playGround, also use playGround ... A playground or playground or PlayGround should generate this error.

I hope this help!