retrieving dbo's Create script

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

retrieving dbo's Create script

Author

Topic

Neil_
Starting Member

13 Posts

Posted - 2008-08-21 : 05:17:20

Hi All,
This is my very first post in this form so forgive me if this is in the wrong place.

:
I am creating a tool for maintaining the data and structure of a database(somewhat mixture between MS Access and Query Analyser + other features).
I need to obtain the Create scripts for all my User Defined:
Tables, Functions, Procedures, Triggers, Views and the script that was used to create the database.

Currently I can only get the scripts for the tables(By re-generating the scripts in my application from meta-data).

Can you please tell me how to retrieve the Create Scripts used to create the object.
Eg:
SELECT * FROM someBuiltInFunction('TheDboObjectName')
Thanx in advance.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-21 : 05:20:56

http://www.developersdex.com/sql/message.asp?p=580&r=6371922

Neil_
Starting Member

13 Posts

Posted - 2008-08-21 : 05:35:32

Hi visakh16,
Thanx for the quick reply.
The problem is I need it to be done Programatially,
Not through Enterprise Manager.

I am a Java Programmer and as such should retrieve the scripts through a query.
Is this possible?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-21 : 06:48:31

quote:
Originally posted by Neil_

Hi visakh16,
Thanx for the quick reply.
The problem is I need it to be done Programatially,
Not through Enterprise Manager.

I am a Java Programmer and as such should retrieve the scripts through a query.
Is this possible?

do you mean you need get entire db scripts including all objects from your java program?

Neil_
Starting Member

13 Posts

Posted - 2008-08-21 : 06:59:07

No, let me explain.

The Application(Tool) will ask the user which DB to connect to.
The Java Application will by executing a query retrieve the dbo objects by name and type.
Then Run the query I require that returns the script for each object.
The Application will then do what it must do with the scripts.

Does this better explain what I need to do.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-21 : 07:20:29

quote:
Originally posted by Neil_

No, let me explain.

The Application(Tool) will ask the user which DB to connect to.
The Java Application will by executing a query retrieve the dbo objects by name and type.
Then Run the query I require that returns the script for each object.
The Application will then do what it must do with the scripts.

Does this better explain what I need to do.

can you simply run sp_helptext object name and get script ?

Neil_
Starting Member

13 Posts

Posted - 2008-08-21 : 07:40:40

OK I will try that
I only have MS Access on my work PC (Currently)
I will try it at home later,
Hope it worx.
I will post my result here in a few hours(aprox 5-6)

Thanx visakh16 for all your efforts.
Neil

Neil_
Starting Member

13 Posts

Posted - 2008-08-21 : 11:08:55

Thanx visakh16,
I tried it and yes it is exactly what I am looking for.
One more thing
I can get the scripts for the tables from processing meta-data,
and now I can get the scripts for the rest
BUT
is there an equivalent to sp_helptext for getting the create table scripts and possibly create database scripts?
The above will just be to simplify/improve on what I already have.

Thanx for all your help

Neil_
Starting Member

13 Posts

Posted - 2008-08-21 : 19:26:50

I have written a SQL SP to retrieve the table scripts.
Currently it finds the field:
Name,
Type,
Max value,
isNull
isPrimaryKey
isReference - ref'd table and field
and generates the scripts

It doesn't yet identify an IDENTITY FILED or Default values.

Neil_
Starting Member

13 Posts

Posted - 2008-08-21 : 19:28:27

CREATE PROCEDURE [TABLE SCRIPTS]
@Name VARCHAR(255)
AS
BEGIN
SELECT @NAME AS [TABLE NAME], COLUMN_NAME, COLUMN_DEFAULT, IS_NULLABLE, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH
INTO #BASE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @Name
ORDER BY ORDINAL_POSITION ASC

SELECT KCU_FK.TABLE_NAME, KCU_FK.COLUMN_NAME, KCU_PK.COLUMN_NAME AS [LINKED FIELD], KCU_PK.TABLE_NAME AS [LINKED TABLE]
INTO #REF_VALUE
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS RC
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KCU_FK
ON RC.CONSTRAINT_NAME = KCU_FK.CONSTRAINT_NAME
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KCU_PK
ON RC.UNIQUE_CONSTRAINT_NAME = KCU_PK.CONSTRAINT_NAME

SELECT TC.TABLE_NAME, CCU.COLUMN_NAME, TC.CONSTRAINT_TYPE
INTO #PKFK
FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE AS CCU
JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC
ON CCU.CONSTRAINT_NAME = TC.CONSTRAINT_NAME

DECLARE @TN VARCHAR(255)
DECLARE @CN VARCHAR(255)
DECLARE @CD VARCHAR(255)
DECLARE @IN VARCHAR(255)
DECLARE @DT VARCHAR(255)
DECLARE @CML INT
DECLARE CUR CURSOR
FOR SELECT [TABLE NAME], COLUMN_NAME, COLUMN_DEFAULT, IS_NULLABLE, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH
FROM #BASE
FOR READ ONLY
DECLARE @SCRIPT VARCHAR(8000)
DECLARE @TEMP_S VARCHAR(255)
DECLARE @TEMP_T VARCHAR(255)
DECLARE @TEMP_F VARCHAR(255)
OPEN CUR
FETCH FROM CUR INTO @TN, @CN, @CD, @IN, @DT, @CML
SET @SCRIPT = 'CREATE TABLE [' + @TN + ']( '
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SCRIPT = @SCRIPT + '[' + @CN + '] ' + @DT
IF(@DT = 'CHAR')
SET @SCRIPT = @SCRIPT + '(' + CONVERT(VARCHAR,@cml) + ')'
IF(@DT = 'VARCHAR')
SET @SCRIPT = @SCRIPT + '(' + CONVERT(VARCHAR,@cml) + ')'
IF(@IN = 'No')
SET @SCRIPT = @SCRIPT + ' NOT'
SET @SCRIPT = @SCRIPT + ' NULL '
/*
PUT IN IDENTITY
*/
SET @TEMP_S = 'OTHER'
SELECT @TEMP_S = CONSTRAINT_TYPE
FROM #PKFK
WHERE ([TABLE_NAME] = @TN) AND ([COLUMN_NAME] = @CN)
IF(@TEMP_S <> 'OTHER')
BEGIN
IF(@TEMP_S = 'PRIMARY KEY')
SET @SCRIPT = @SCRIPT + 'PRIMARY KEY'
IF(@TEMP_S = 'FOREIGN KEY')
BEGIN
SET @SCRIPT = @SCRIPT + 'REFERENCES ['
SELECT @TEMP_T = [LINKED TABLE], @TEMP_F = [LINKED FIELD]
FROM #REF_VALUE
WHERE ([TABLE_NAME] = @TN)AND([COLUMN_NAME] = @CN)
SET @SCRIPT = @SCRIPT + @TEMP_T + ']([' + @TEMP_F + '])'
END
/*
PUT IN DEFAULT VALUE
*/
END
FETCH FROM CUR INTO @TN, @CN, @CD, @IN, @DT, @CML
IF(@@FETCH_STATUS = 0)
SET @SCRIPT = @SCRIPT + ', '
END
SET @SCRIPT = @SCRIPT + ')GO'
SELECT @SCRIPT
CLOSE CUR
DEALLOCATE CUR
DROP TABLE #BASE, #REF_VALUE, #PKFK
END
GO

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources