SQL Server gives a very useful tool ‘Database Publish Wizard’ that can be used to script and publish SQL Server database. For SQL Server 2005 installation, this tool is usually located at **[Program Files]\Microsoft SQL Server\90\Tools\Publishing\SqlPubWiz.exe. **This tool has both command line as well as GUI interface.

Here I am going to show some sample command line queries that can be used to generate database scripts. For the sake of example, let us assume that database name is “MyDatabase” which is hosted on SQL Server instance “SQLEXPRESS” on the local machine.

Following command will give you script for schema (not data) of the database. The script is output to a text file C:\Script.txt.

SqlPubWiz.exe script -S .\SQLEXPRESS -d MyDatabase -schemaonly -f C:\Script.txt

Option “-S” specifies the SQL Server instance. Options “-d” specifies the database to script. Options “-schemaonly” specifies that we want to script only schema. Options “-f” specifies the output file.

SqlPubWiz by default works in windows authentication mode. So if your Server does not allow for windows authentication, you will also need to specify user name and password with “-U” and “-P” option respectively.

In the same way as above command, following command will generate script for all the data of the database. It will not contain any schema script.

SqlPubWiz.exe script -S .\SQLEXPRESS -d MyDatabase -dataonly -f C:\Script.txt

We can even use Management Studio to generate database scripts (Right click database, select Task and then select Generate Scripts). However, there is no options in management studio to generate “dataonly” scripts in which case this tool comes handy.

Following SQL query can be used to list all the primary keys of a table. In this given example, we assume that table name is ‘Employee’.

SELECT SysColumns.Name
FROM SysIndexes SysIndexes
JOIN SysObjects SysObjects ON SysIndexes.Id = SysObjects.Id
JOIN SysObjects PrimaryKey ON SysIndexes.Name = PrimaryKey.Name
AND PrimaryKey.Parent_Obj = SysIndexes.Id
AND PrimaryKey.Xtype = 'PK'
JOIN SysIndexKeys SysIndexKeys on SysIndexes.Id = SysIndexKeys.Id
AND SysIndexes.IndId = SysIndexKeys.IndId
JOIN SysColumns SysColumns ON SysIndexKeys.Id = SysColumns.Id
AND SysIndexKeys.ColId = SysColumns.ColId
WHERE SysObjects.name = 'Employee'
ORDER BY SysIndexKeys.keyno

Following SQL query can be used to list all the default constraints of a tabled named ‘Department’:

SELECT DefaultConstraints.Name 'Constraint Name', DefaultConstraints.Definition 'Default Value' 
FROM Sys.Default_Constraints DefaultConstraints INNER JOIN Sys.Objects SystemObjects 
On SystemObjects.Object_Id = DefaultConstraints.Parent_object_Id
Where SystemObjects.Name = 'Department'

Following SQL statement can be used to check if stored procedure named “SelectEmployee” exists in the active database and drop it if exists.

IF EXISTS (SELECT SystemObjects.Name
FROM SYS.OBJECTS SystemObjects 
WHERE SystemObjects.type = 'p' AND SystemObjects.Name = 'SelectEmployee')
BEGIN
DROP PROCEDURE SelectEmployee
END

Following SQL statement can be used to check if table name “Employee” exists in the database and drop it if exists.

IF EXISTS (SELECT * FROM Sys.Tables
WHERE name = 'Employee')
BEGIN
DROP TABLE Employee;
END

Following SQL statement checks to see if column “Address” exists in the “Employee” table. If the column does not exist it adds that column.

If Not Exists (Select * From Information_Schema.Columns 
               Where Table_Name = 'Employee' And Column_Name = 'Address')
Begin
    Alter Table Employee Add Address nvarchar(200)
End

Often we need to get all the Foreign Key constraints so that you can drop them by making dynamic queries and then re-add them. This is usually needed when you are writing some custom data migration tool in which case you drop all the foreign key constraints first, then populate data, and then you add the constraints again.

Whatever be the use case, following SQL query will give you details of all the foreign key constraints in the active database:

SELECT OBJECT_NAME(ForeignKeyColumn.Constraint_Object_ID) AS 'ForeignKey', OBJECT_NAME(ForeignKeyColumn.Parent_Object_ID) AS 'ForeighKeyTable', ParentColumn.name AS 'ForeighKeyColumn', OBJECT_NAME(ForeignKeyColumn.Referenced_Object_ID) AS 'PrimaryKeyTable', ReferencedColumn.Name AS 'PrimaryKeyColumn' 
FROM Sys.Foreign_Key_Columns ForeignKeyColumn
INNER JOIN Sys.All_Columns ParentColumn 
ON ForeignKeyColumn.Parent_Object_ID = ParentColumn.Object_ID
AND ForeignKeyColumn.Parent_Column_ID = ParentColumn.Column_ID
INNER JOIN Sys.All_Columns ReferencedColumn 
ON ForeignKeyColumn.Referenced_Object_ID = ReferencedColumn.Object_ID
AND ForeignKeyColumn.Referenced_Column_ID = ReferencedColumn.Column_ID 
ORDER BY OBJECT_NAME(ForeignKeyColumn.Referenced_Object_ID);

Following SQL query will list all the stored procedures and their definition in the active database:

SELECT SysObject.Name, SysModule.Definition
FROM Sys.Objects SysObject INNER JOIN Sys.Sql_Modules SysModule
ON SysObject.Object_ID = SysModule.Object_ID
WHERE SysObject.Type = 'p'
ORDER BY SysObject.Name;

Following SQL query gives you names of all the stored procedures in the active database:

SELECT SystemObjects.Name
FROM SYS.OBJECTS SystemObjects 
WHERE SystemObjects.type = 'p' 
ORDER BY SystemObjects.Name;

Following SQL query gives you names of all the tables in the currently active database:

SELECT Name FROM Sys.Tables

Sometimes, you get this error when installing SQL Server 2005. Following is what has fixed the issue for me:

• Go to windows services (Start->Run->services.msc) and start Windows Installer service if it is not already started. If you are not able to start Windows Installer service, run **msiexec /regserver **command from command prompt.