How to find a COLUMN name and the Condition Existing...

Question

Post reply

How to find a COLUMN name and the Condition Existing...

- Win.

SSCertifiable

Points: 6790
More actions
July 23, 2009 at 3:02 am

#134459

Dear Experts,
Please help me in fixing this :
I Have a database TESTDB ;
Contains 800+ tables ; which has "DateCreated" as a column name in more than 600 tables,
column name as 'StateID' contains a status like '%RequestCompleted%'.
My Criteria is like : I would like to write a query which needs to Delete the records, contains column name as "DateCreated" and if doesnt exists the same, it should SKIP to the next table and check. If exists on the next table then it should Delete the rows.
Another column 'StateID' contains like '%RequestCompleted%' should also be deleted depends on the criteria that if exists like '%RequestCompleted%'.
Please provide the inputs and help me to fix this. Writing a .NET code for this , its a requirement in thecode to search the two conditions
1. Column name : DateCreated
2. Column name StateID : contains like '%RequestCompleted%' ; should be deleted from all the tables for a given Database.
Fast Help is appreciated...
Cheers,
- Win.
" Have a great day "

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Johan Bijnens SSC Guru Points: 134873 More actions · Answer 1

use at your own risk 😉

You could use this to start with

Select 'Delete from [' + TABLE_CATALOG + '].[' + TABLE_SCHEMA + '].[' + TABLE_NAME + '] where [' + COLUMN_NAME +'] < .... ;'

from INFORMATION_SCHEMA.COLUMNS

--Where TABLE_NAME = @TbName

Where COLUMN_NAME like '%YourNameToBeSearched%'

order by TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, ORDINAL_POSITION;

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Lowell SSC Guru Points: 323482 More actions · Answer 2

i read the requirement a bit different; that the table in question had to have two specific column names in it to build the sql: I added a where statement for the datecreated field as well:

Select 'Delete from [' + OBJCOL.TABLE_NAME + '] where [' + OBJCOL.COLUMN_NAME +'] like ''%RequestCompleted%'' AND [' + SECCOL.COLUMN_NAME +'] BETWEEN ''01/01/2008'' AND ''01/01/2009'';'

from INFORMATION_SCHEMA.COLUMNS OBJCOL

INNER JOIN INFORMATION_SCHEMA.COLUMNS SECCOL ON OBJCOL.TABLE_NAME=SECCOL.TABLE_NAME

AND SECCOL.COLUMN_NAME = 'DateCreated'

--Where TABLE_NAME = @TbName

Where OBJCOL.COLUMN_NAME ='StateID '

order by OBJCOL.TABLE_CATALOG, OBJCOL.TABLE_SCHEMA, OBJCOL.TABLE_NAME, OBJCOL.ORDINAL_POSITION;

--results

Delete from [AllWords] where [StateID] like '%RequestCompleted%' AND [DateCreated] BETWEEN '01/01/2008' AND '01/01/2009';

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Johan Bijnens SSC Guru Points: 134873 More actions · Answer 3

Great addition Lowel :w00t:

Now all OP has to do is copy/paste and hit F5.

One last remark, if these tables contain many rows and you cannot perfrom "truncte table ..." but will delete a vast amount of GB, maybe it's better to take a full backup, switch your db to simple recovery, and generate limited row batches to be executed until no rows to be deleted occurs.

e.g.

Declare @BatchSize int

Set @BatchSize = 5000 -- Modify as needed !!!

Set nocount on

declare @RowsDeleted bigint

Declare @MyRowcount bigint

set @RowsDeleted = 0

while 0 = 0

begin

DELETE top ( @BatchSize )

FROM

WHERE

set @MyRowcount = @@rowcount

if @MyRowcount = 0 break

select @RowsDeleted = @RowsDeleted + @MyRowcount

end

Print '[' + cast(@RowsDeleted as varchar(15)) + '] Rows deleted.'

Keep in mind to switch bak to your original recovery model and take a full backup afterward !

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

- Win. SSCertifiable Points: 6790 More actions · Answer 4

Thanks a lot for all.

Let me try and update you the same soon.

Rgds,

Win

Cheers,
- Win.

" Have a great day "