SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Server query Automation


SQL Server query Automation

Author
Message
krishna30
krishna30
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 16
Hi,

I am testing one application I am preparing some queries as given below

Step1:
SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE WHERE TABLE_NAME = 'CMC_PDRA_RATE_AGES'
Step 1 Output

COLUMN_NAME
___________
HIST_ROW_ID
PDRA_AGE_FROM
PDRA_REF_DTM

Step 2:
Use the Columns name from step 1 and create a delte statement as given below.

DELETE FROM [FACETS_TEST].CMC_PDRA_RATE_AGES WHERE HIST_ROW_ID = '' AND PDRA_AGE_FROM = '' AND PDRA_REF_DTM = '';

I have about 300 tables like to do. Is there any automated SQL statement that someone can recommend.

THanks,
Krishna
CapnHector
CapnHector
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1351 Visits: 1789
What you are asking is possible through the blessing and curse of dynamic sql. before you start in on dynamic sql have a read through this as it has a detailed section on SQL Injection risks when using dynamic sql along with a good primer on dynamic sql.

http://www.sommarskog.se/dynamic_sql.html


For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden for the best way to ask your question.

For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw

Need to Split some strings? Jeff Moden's DelimitedSplit8K
Jeff Moden's Cross tab and Pivots Part 1
Jeff Moden's Cross tab and Pivots Part 2
GilaMonster
GilaMonster
SSC Guru
SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)

Group: General Forum Members
Points: 88268 Visits: 45277
I strongly recommend you do not go that design path. It's possible, it's not going to be fun and honestly it's not that good of an idea.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


krishna30
krishna30
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 16
I am kind of new in writing Procedure's or dynamic SQL's. Any suggestion that you could help me out with.

This manual work is just making me go crazy Sad
George M Parker
George M Parker
SSC Eights!
SSC Eights! (846 reputation)SSC Eights! (846 reputation)SSC Eights! (846 reputation)SSC Eights! (846 reputation)SSC Eights! (846 reputation)SSC Eights! (846 reputation)SSC Eights! (846 reputation)SSC Eights! (846 reputation)

Group: General Forum Members
Points: 846 Visits: 1472
I concur with Gail on this one. If you are trying to come up with a one time process to generate the delete statements to incorporate into some other type of code, then it might be worth considering. But if you are trying to repeat the delete statement generation process, you might want to rethink your design.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)

Group: General Forum Members
Points: 88268 Visits: 45277
krishna30 (10/30/2012)
I am kind of new in writing Procedure's or dynamic SQL's. Any suggestion that you could help me out with.


A procedure should do a single thing. The academic term is 'single responsibility'

If writing in C# or java or the like, you wouldn't write a class that can do one of 20 things depending on the parameters, that would be against so many coding practices. Same thing with SQL, don't write a procedure that depending on the parameters passed selects an employee, updates an order, deletes a product or drops the database.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search