Dynamic DDL generation - SQL Server Forums

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)

Dynamic DDL generation

Author

Topic

clarkbaker1964
Constraint Violating Yak Guru

428 Posts

Posted - 2004-09-13 : 20:38:40

I need to create a stored procedure to generate a tables ddl...

I am working with a denormalized database that has a complex structure, my task is to delete all records from the database where the user has entered the description "DELETE".

Prior to running the delete I want to store the data from each table into a table with the same structure so I can recover the information if the user has made a mistake.

Their are approximately 45 tables which contain the key field but may or may not contain records that I will be deleting. I would like to generate the DDL on the table then modify the result string for the new target table prior to appending the record and subsequently deleting it.

Manually generating the DDL from Query Builder is getting tedious.

Thank you in advance

If the Select * Into will create a target table with the same structure as the source this would be acceptable... Just not sure if it creates the same structure.

hgorijal
Constraint Violating Yak Guru

277 Posts

Posted - 2004-09-14 : 02:12:27

Select..into will create the same table structure.. but it will not recreate your keys and relationships.

the whole setup seems bit fuzzy to me. Instead, you can probably try using soft delete, by flaging the record in the table as deleted (add a new column to the table and update it when the used wants to delete the record). This way the data is still intact to rollback at any user error.

Hemanth Gorijala
BI Architect / DBA

clarkbaker1964
Constraint Violating Yak Guru

428 Posts

Posted - 2004-09-14 : 14:19:41

Thank you for the suggestion I tried implementing the Select into logic last night and found that it created exception errors due to Varchar field default length was based on the longest string in the first query pass...

The true nature of my question is this...

How do I create the DDL on any table from a stored procedure?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-09-14 : 14:32:21

quote:

How do I create the DDL on any table from a stored procedure?

Use the Generate SQL Script wizard.

Tara

clarkbaker1964
Constraint Violating Yak Guru

428 Posts

Posted - 2004-09-14 : 15:30:22

Yea that was the easy answer... I appreciate your input on this, I'm just worried I will miss something in a cascade path I have about 30 staging tables that have no relationships so between developing the inserts/deletes and DDL I was tring to come up with a somewhat automated approach. The cascade deletes have never been tested and I don't trust them so I was going to work bottom up.

X002548
Not Just a Number

15586 Posts

Posted - 2004-09-14 : 15:39:39

OK...you gotta let us in...WHY are you letting users delete tables?

Sounds kinda dangerous...not to mention messy...

Tara: He's From CA...this should be right up your alley...

Anyway..it sounds like the tables have relationships as well?

What about views?

I'm not saying it can't be done (I've got some code lying around somewhere for dropping and recreating the RI), but why?

What's it for, and what's the purpose?

Brett

8-)

clarkbaker1964
Constraint Violating Yak Guru

428 Posts

Posted - 2004-09-14 : 15:52:51

Yea I surf to... Actually the users are deleting records but I have adopted a PWC nightmare... No functionality was integreated into the front end to allow deletes that would then be handled properly by the db. Users are now changing the project description to "PLEASE DELETE" this would be at the mile high level when looking at all of the detail tables...
Now I have to determine where the Project_ID exists in all tables and remove the related records.

So I wrote the following to identify the tables containing the proper columns the next step would be to check to see if it contains records I need to delete and save those into target tables with the same structure in case they have f up.

-- =============================================
-- Declare and using a READ_ONLY cursor
-- =============================================
DECLARE cursor_ProjectIDTables CURSOR FAST_FORWARD
FOR Select o.Name from sysobjects o, syscolumns c where o.id = c.id and c.Name = 'PROJECT_ID' and o.xtype = 'U'

DECLARE @name varchar(40)
OPEN cursor_ProjectIDTables

FETCH NEXT FROM cursor_ProjectIDTables INTO @name
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
-- PRINT 'add user defined code here'
-- eg.
DECLARE @message varchar(100)
SELECT @message = 'my name is: ' + @name
PRINT @message
END
FETCH NEXT FROM cursor_ProjectIDTables INTO @name
END

CLOSE cursor_ProjectIDTables
DEALLOCATE cursor_ProjectIDTables
GO

This cursor shows me all tables where the Project ID exists currently I have 60... not all of these tables will have matching data some are garbage left over from developers turning over or creating apps that are no longer utilizing these tables.

Where are you from? ;)

clarkbaker1964
Constraint Violating Yak Guru

428 Posts

Posted - 2004-09-14 : 16:00:36

50+ tables... With the Id existing in the table... more tables containing foreign keys that I may be able to find if I walk the relationships.

1 stg_pl_actuals_bk
2 STG_PL_AMORT
3 PSFT_PL_ACTUAL_test_OLD
4 PSFT_PL_AMORT
5 STG_PL_ACTUALS_test_OLD
6 PSFT_CF_ACTUAL_test_OLD
7 STG_CF_ACTUALS_test_OLD
8 PART_RESID_RATES
9 CF
10 STG_CF_ACTUALS
11 STG_PL_ACTUALS
12 PROJECT_LIST
13 PL
14 DELIVERY_SLATE
15 PROJECT_prod_bk
16 project_bk2
17 project_frank
18 project_bk
19 pl_frank
20 frankXMLpl
21 UPGT_TEST
22 xDelPrj_DELIVERY_SLATE
23 GREEN_LIGHT_ULTIMATES
24 pv_Contracts
25 xDelPrj_PROJECT_LIST
26 xDelPrj_CF
27 xDelPrj_FIN_ULTIMATES
28 xDelPrj_MGT_ULTIMATES
29 xDelPrj_GREEN_LIGHT_ULTIMATES
30 xDelPrj_PL
31 PSFT_PL_ACTUAL
32 PROJECT
33 PSFT_CF_ACTUAL
34 STG_AMORT_LOAD_TO_PSFT
35 frank_pl
36 STG_AMORTIZATION
37 xDelPrj_PROJECT
38 STG_DOM_SYN_PLCF
39 xDelPrj_Validate
40 STG_ULTIMATES_FEED
41 STG_ULTIMATES_PL_VIEW
42 UPGT_ULT_LOAD_TBL_BEFORE
43 STG_DOM_SYN_ULT
44 QUERY$
45 STG_PROJECTS
46 DelMgtUlt
47 DelFinUlt
48 DelPL
49 DelCF
50 DelProjList
51 MGT_ULTIMATES
52 DelDeliverySlate
53 FIN_ULTIMATES

clarkbaker1964
Constraint Violating Yak Guru

428 Posts

Posted - 2004-09-14 : 16:04:13

Hey you know whats even funnier than being from CA... I have got to sort out this s^# in a Top 5 Entertainment company!

X002548
Not Just a Number

15586 Posts

Posted - 2004-09-14 : 16:05:41

Where am I from?

Click on the profile....actually depends on the day...could be pluto somedaze..

Anyway...it seems because you dynamically have to know what tables are what, seems to tell me they're being added on the fly...ugh..

Let's hope not...

If not, then seems to be a GREAT idea for the use of views....

Create the view...and do nothing....

Brett

8-)

clarkbaker1964
Constraint Violating Yak Guru

428 Posts

Posted - 2004-09-14 : 16:11:00

Tables are not being added on the fly... thank god but why do companies like PWC create such trash... I have some foreign keys containing 8 fields just to carry a relationship for 1 note field.

clarkbaker1964
Constraint Violating Yak Guru

428 Posts

Posted - 2004-09-14 : 16:12:30

The view would be the easiest part if I could rewrite the front end application for all of the grids, combo boxes, screens etc.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-09-14 : 16:14:18

quote:
Originally posted by clarkbaker1964

Tables are not being added on the fly... thank god but why do companies like PWC create such trash... I have some foreign keys containing 8 fields just to carry a relationship for 1 note field.

PWC, is that Price Waterhouse Cooper? If so, don't get me started. We used them at my last job to "enhance" a document management system.

Tara

clarkbaker1964
Constraint Violating Yak Guru

428 Posts

Posted - 2004-09-14 : 16:17:38

Hey I knew CA wasn't the only place they dropped their stink.

Surf up Dude!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources