7

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?

veducm
  • 5,933
  • 2
  • 34
  • 40
tomsv
  • 7,207
  • 6
  • 55
  • 88
  • I would like to do this from a script. In Mysql I would do "show create table exampletablename" for each table. – tomsv Dec 28 '10 at 09:21

6 Answers6

6

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.

Jagmag
  • 10,283
  • 1
  • 34
  • 58
  • I get an error from this when I try to publish the schema:Status der Datenbankveröffentlichung - Liste der Objekte für die Skripterstellung wird zusammengestellt (Erfolg) - Skripterstellung für Objekte (Fehler) Meldungen Operation is not valid due to the current state of the object. (SqlPubWiz) - Skript wird auf den Datenträger geschrieben (Beendet) (Sorry for the German error messages) – tomsv Dec 28 '10 at 09:17
5

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.

Leigh
  • 28,765
  • 10
  • 55
  • 103
Whimsical
  • 5,985
  • 1
  • 31
  • 39
2

If you need to get script from T-SQL then only using xp_cmdshell. For example scripting concreate index of concreate view with SMO and powershell (result is in @script variable, you could 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

P.S. For those who asks why we could need such tricks: in some scenarios, e.g. "import data using third party tool", the approach "drop-recreate" works better than "enable-disable" objects, e.g. because such third party tool can call "truncate" and if your table participates in schema-bound view you will get a third party tool error (truncating table participating in indexed views throws an error, therefore we are forced to drop the view with all indexes before import and recreate it after).

Roman Pokrovskij
  • 9,449
  • 21
  • 87
  • 142
2

How to find foreign key dependencies in SQL Server?

Community
  • 1
  • 1
John Sansom
  • 41,005
  • 9
  • 72
  • 84
1

As an alternative to InSane's perfect answer, you can right click any object in SSMS to script it to a text file or a window.
A few free and non-free products also allow you to this, including WinSQL.

iDevlop
  • 24,841
  • 11
  • 90
  • 149
0

I have seen the comment or reply of the user "dontomoso". After translating to English it seems that the "Operation is not valid due to the current state of the object. (SqlPubWiz)" in "database publishing wizard" is the error experienced.

After so many experiments and trials, in this app. the database name is case sensitive. Put correct case value for the -d parameter. The solution is simple, change the default schema name or Initial Catalog name same as "used while Create DB". E.g. While creating if playGround is used, use playGround here too...Playground or playground or PlayGround shall generate this error.

I hope this help!

Sudhir Gulhane
  • 11
  • 2