Automatic SQL Statements - 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

General SQL Server Forums

New to SQL Server Programming

Automatic SQL Statements

Author

Topic

krishnaraaj
Starting Member

1 Post

Posted - 2012-10-30 : 17:55:01

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

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-10-30 : 20:01:02

[code]SELECT 'DELETE FROM ' + QUOTENAME(t.table_schema) + '.' + quotename(t.table_name) + ' WHERE ' +
STUFF((SELECT ' AND ' + QUOTENAME(column_name) + '= ''''''
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_schema=t.table_schema AND table_name=t.table_name
and character_maximum_length IS NOT NULL FOR XML PATH('')),1,5,'') + ';'
FROM INFORMATION_SCHEMA.TABLES t
WHERE t.TABLE_TYPE='BASE TABLE'[/code]Warning: not tested, might throw syntax errors. If not, it will generate the DELETE statements you requested.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources