Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Apr 29, 2005

Generate Script for objects and dependent objects

By Muthusamy Anantha Kumar aka The MAK

SQL Server database administrators often deploy object changes, such as table, procedure, trigger etc., on the production system. Usually when they do, they check for dependencies, just to make sure that those changes are not going to break the system.

This article examines how to find most of the possible dependencies and generate a script for those objects and it dependent objects. This article will illustrate how to take advantage of SQL DMO, T-SQL and VBScript to accomplish this.


a.  SQL Server Client Installed on the machine where you are running this script

b.  Make sure the login used in this script has enough access to all the servers, databases and Objects listed in the Objectlist.txt. It is advisable to use SA login.

c.  Make sure there is no restriction in executing VBScript on the machine where you are executing this script.

d.  Make sure the windows login used for log on to this machine has enough access to create folders and files.

Step 1

Create folder C:\SQLServerDeploy and C:\SQLServerDeploy\Scripts. [Refer Fig 1.0]

MD C:\SQLServerDeploy
MD C:\SQLServerDeploy\Scripts

Fig 1.0

Step 2

Download GenDependScript.vbs and save the file to the C:\SQLServerDeploy folder. [Refer Fig 1.1]

Fig 1.1

Step 3

Create C:\SQLServerDeploy\Objectlist.txt and enumerate all of the objects that are undergoing changes due to deployment. [Refer Fig 1.2]


Make sure that you use a four part qualified name as shown.

Fig 1.2

Note: Four part name = Server.Database.Owner.Objectname

If you want to script any indexes, please specify the index name as shown below:


Step 4

Execute the GenDependScript.vbs as shown in Fig 1.3.

cscript GenDependScript.vbs sa pwd c:\sqlserverdeploy\objectlist.txt 1556 c:\sqlserverdeploy\scripts\

Fig 1.3

Parameters Explained

Type GenDependScript.vbs at the command prompt, to display the following message. Refer Fig 1.4

Fig 1.4

GenDependScript.vbs = Script name
Login = Login to the server listed in the four part name in Objectlist.txt. Usually SA.
Password = Password for the login used
ObjectlistFile = Location and File name of all of the Objects enumertated for deployment.
Build# = This is the number or text used for any deployments. In some companies, it is called the Change Management Number, in some it is called the Build promotion number and in some companies, it is called the Production release number.
Destination Folder = This is the folder where all of the scripts are going to be generated.

GenDependScript.vbs completes by displaying the message shown in Fig 1.5.

Fig 1.5

GenDependScript.vbs generates the log file shown in Fig 1.6 and 1.7.

Fig 1.6

Fig 1.7

Download Log File

GenDependScript.vbs also creates an individual folder for every object that was listed in Objectlist.txt. Refer Fig 1.8

Fig 1.8

GenDependScript.vbs also generates scripts for the Object and its entire dependent object, including cross database dependencies. Refer Fig 1.9 and Fig 2.0 and Fig 2.1

Fig 1.9

Fig 2.0

Fig 2.1

GenDependScript.vbs generates cross database dependencies. For example, if I am scripting the Object "Mytable" in the database "MyDatabase" and if the Object "Mytable" in the database is being used by the "Rproc" procedure in the database "Mydatabase5," GenDependScript.vbs generates the Script for the Object "Mytable" in the database "MyDatabase" and the "Rproc" procedure in the database "Mydatabase5."


This article illustrates how to take advantage of SQL DMO, T-SQL and VBScript to find most of the possible dependencies and generate a script for those objects and it dependent objects.

» See All Articles by Columnist MAK

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM