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.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-04-23 : 12:56:07
|
| Dave writes "SQL Server 7.0, SP2I have a bunch of similarly named tables in a SQL db that are copies of some base tables but then have a few extra fields. In each case, the field names and types of the extra fields are the same across each similarly named table.How do I write a sproc that deletes rows from these tables based on a flag criteria (the flag is one of the extra fields not in the base table)?Having a number of statements that delete from each table is not a good solution since I can't be sure that every table will exist every time the sproc is executed. Nor can I be sure that extra tables of this type haven't been added. I can do an if exists from the system catalog but I'm wondering if there is a better way that addresses the dynamic characteristics of the problem.Thank you for your help." |
|
|
jcelko
Esteemed SQL Purist
547 Posts |
Posted - 2002-04-23 : 13:27:12
|
quote:
Dave writes "SQL Server 7.0, SP2I have a bunch of similarly named tables in a SQL db that are copies of some base tables but then have a few extra fields. In each case, the field names and types of the extra fields are the same across each similarly named table.How do I write a sproc that deletes rows from these tables based on a flag criteria (the flag is one of the extra fields not in the base table)?Having a number of statements that delete from each table is not a good solution since I can't be sure that every table will exist every time the sproc is executed. Nor can I be sure that extra tables of this type haven't been added. I can do an if exists from the system catalog but I'm wondering if there is a better way that addresses the dynamic characteristics of the problem.Thank you for your help."
--CELKO--Joe Celko, SQL Guru |
 |
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2002-04-23 : 18:40:57
|
| You might want to look into the INFORMATION_SCHEMA.Tables view. It lists the tables that exist in the database. Perhaps you can do a select on it, maybe populate a temp table with the few tablenames that you're going to modify, and then build some Dynamic SQL to handle the processing. You can learn more about INFORMATION_SCHEMA views in BOL, and Dynamic SQL here on SQL Team (just do a search). |
|
|
|
rrb
SQLTeam Poet Laureate
1479 Posts |
Posted - 2002-04-23 : 20:22:36
|
Actually I think Joe said it all - And he only said what you said... --I hope that when I die someone will say of me "That guy sure owed me a lot of money" |
|
|
|
Nazim
A custom title
1408 Posts |
Posted - 2002-04-24 : 00:37:04
|
select 'delete from ' + table_name +' where flag=1'from INFORMATION_SCHEMA.Tables copy and execute the result of this query.Can you clarify on thisquote:
How do I write a sproc that deletes rows from these tables based on a flag criteria (the flag is one of the extra fields not in the base table)?
-------------------------------------------------------------- |
|
|
|
Yutzmann
Starting Member
10 Posts |
Posted - 2002-04-26 : 08:36:09
|
How would I do the kind of operation you suggest in a sproc? I know I can do it in SQL QA but how do I automate the cut and paste? Do I set a variable and then nest it in another var to execute the results? Is it possible to execute the results of a query?I used the sysobjects table in the db to return the objects that have the similar naming scheme. I then popped this into a cursor and processed using the FETCH commands. I will look in this view. I was a little leary putting the sproc into production without being able to control what type of object I was deleting from. Wondering if the view will return more info in the object type (table, sproc, function, etc.)One point that may help others. When I executed the statement,delete from @tablename where flag = '1'I got a syntax error. I used a hint from the forum to wrap my statement in quotes, set it to a variable and then execute the string using EXEC( @statement ). Worked like a charm after that.Thanks for all the help. Great forum!  |
|
|
|
|
|
|
|
|
Deleting rows from tables based on table name