Looping through tables in a db

Hello - I am somewhat new to stored procedures, so please be patient. I need to know how to go about wring a stored proc to loop through all of the tables in a db and delete the records in them.

I'm assuming a stored procedure is the best way to do this. If there is a better way, please let me know

I think I'm on the right track with this below

/* Create in each database that it is used in */

CREATE PROC usp_DBCCCheckTable

AS

/* Declare Variables */

DECLARE @.v_table sysname,

@.v_SQL NVARCHAR(2000)

/* Declare the Table Cursor (Identity) */

DECLARE c_Tables CURSOR

FAST_FORWARD FOR

SELECT name

FROM sysobjects obj (NOLOCK)

WHERE type = 'U'

OPEN c_Tables

Try

CREATE PROCEDURE DeleteYourTableRecords
AS
BEGIN
declare @.SQL_Str nvarchar(max)
set @.SQL_Str=''
SELECT @.SQL_Str=@.SQL_Str +'delete from ' + name +';' from sys.tables
EXECUTE sp_executesql @.SQL_Str
END
GO|||

I cant dare running this on my machine so here you go with PRINT statement. just uncomment EXEC if you dare.

Sorry i dont like CURSORS so here you go ... hope this will help

DECLARE @.TABLE_NAME VARCHAR(500)

DECLARE @.CMD VARCHAR(MAX)

SELECT TOP 1

@.TABLE_NAME = TABLE_NAME

FROM

INFORMATION_SCHEMA.TABLES

WHERE

TABLE_TYPE = 'BASE TABLE'

ORDER BY

OBJECT_ID(TABLE_NAME)

WHILE @.@.ROWCOUNT > 0

BEGIN

SET @.CMD = 'TRUNCATE TABLE ' + @.TABLE_NAME

PRINT @.CMD

--EXEC(@.CMD)

SELECT TOP 1

@.TABLE_NAME = TABLE_NAME

FROM

INFORMATION_SCHEMA.TABLES

WHERE

OBJECT_ID(TABLE_NAME) > OBJECT_ID(@.TABLE_NAME)

END

|||

Be sure you want to "DELETE" rather than "TRUNCATE"

Here's the code to do it. Don't run this unless you REALLY REALLY want to delete all the data from all your tables.

Code Snippet

select 'delete from [' + U.Name + '].[' + O.Name + '];' as [CMD]

into #CMDS

from sysobjects O

inner join sysusers U on U.UID = o.UID

where O.xType = 'U'

declare @.cmd varchar(255)

while exists(select * from #cmds)

begin

select top 1 @.cmd = cmd from #cmds

exec(@.cmd)

delete from #cmds where cmd = @.cmd

end

drop table #cmds

|||

In most cases, it is not as simple as deleting from each table in the database. If there are foreign key references then you need to delete data in a particular order unless you have cascading actions (it depends on the schema and it doesn't work for all types of relationships). Otherwise, you will get errors when you try to delete rows that are referenced by other tables. And truncate table as suggested in other replies is more restrictive. Lastly, what happens to any business logic in triggers? Do you need to unnecessarily execute them?

So what are you trying to do exactly? Are you trying to cleanup the database? It is better to just recreate the db from scratch using the scripts from your source code control system.

|||

I think that it will be fairly easy to just delete the rows in the tables. The way that my company's software is set up, there aren't many foreign keys. It's a long story and a dba would have a fit looking at it. I'm fairly used to now though. We do most of our linking through code.

Anyway, when we install a new system, we go through and delete all of our test data out of the tables and sometimes that can take awhile. I thought, since I'm trying to get better at stored procedures anyway, that it would be useful to write one to delete the rows out of the tables.

Looping through tables in a db

Hello - I am somewhat new to stored procedures, so please be patient. I need to know how to go about wring a stored proc to loop through all of the tables in a db and delete the records in them.

I'm assuming a stored procedure is the best way to do this. If there is a better way, please let me know

I think I'm on the right track with this below

/* Create in each database that it is used in */

CREATE PROC usp_DBCCCheckTable

AS

/* Declare Variables */

DECLARE @.v_table sysname,

@.v_SQL NVARCHAR(2000)

/* Declare the Table Cursor (Identity) */

DECLARE c_Tables CURSOR

FAST_FORWARD FOR

SELECT name

FROM sysobjects obj (NOLOCK)

WHERE type = 'U'

OPEN c_Tables

Try

CREATE PROCEDURE DeleteYourTableRecords
AS
BEGIN
declare @.SQL_Str nvarchar(max)
set @.SQL_Str=''
SELECT @.SQL_Str=@.SQL_Str +'delete from ' + name +';' from sys.tables
EXECUTE sp_executesql @.SQL_Str
END
GO|||

I cant dare running this on my machine so here you go with PRINT statement. just uncomment EXEC if you dare.

Sorry i dont like CURSORS so here you go ... hope this will help

DECLARE @.TABLE_NAME VARCHAR(500)

DECLARE @.CMD VARCHAR(MAX)

SELECT TOP 1

@.TABLE_NAME = TABLE_NAME

FROM

INFORMATION_SCHEMA.TABLES

WHERE

TABLE_TYPE = 'BASE TABLE'

ORDER BY

OBJECT_ID(TABLE_NAME)

WHILE @.@.ROWCOUNT > 0

BEGIN

SET @.CMD = 'TRUNCATE TABLE ' + @.TABLE_NAME

PRINT @.CMD

--EXEC(@.CMD)

SELECT TOP 1

@.TABLE_NAME = TABLE_NAME

FROM

INFORMATION_SCHEMA.TABLES

WHERE

OBJECT_ID(TABLE_NAME) > OBJECT_ID(@.TABLE_NAME)

END

|||

Be sure you want to "DELETE" rather than "TRUNCATE"

Here's the code to do it. Don't run this unless you REALLY REALLY want to delete all the data from all your tables.

Code Snippet

select 'delete from [' + U.Name + '].[' + O.Name + '];' as [CMD]

into #CMDS

from sysobjects O

inner join sysusers U on U.UID = o.UID

where O.xType = 'U'

declare @.cmd varchar(255)

while exists(select * from #cmds)

begin

select top 1 @.cmd = cmd from #cmds

exec(@.cmd)

delete from #cmds where cmd = @.cmd

end

drop table #cmds

|||

In most cases, it is not as simple as deleting from each table in the database. If there are foreign key references then you need to delete data in a particular order unless you have cascading actions (it depends on the schema and it doesn't work for all types of relationships). Otherwise, you will get errors when you try to delete rows that are referenced by other tables. And truncate table as suggested in other replies is more restrictive. Lastly, what happens to any business logic in triggers? Do you need to unnecessarily execute them?

So what are you trying to do exactly? Are you trying to cleanup the database? It is better to just recreate the db from scratch using the scripts from your source code control system.

|||

I think that it will be fairly easy to just delete the rows in the tables. The way that my company's software is set up, there aren't many foreign keys. It's a long story and a dba would have a fit looking at it. I'm fairly used to now though. We do most of our linking through code.

Anyway, when we install a new system, we go through and delete all of our test data out of the tables and sometimes that can take awhile. I thought, since I'm trying to get better at stored procedures anyway, that it would be useful to write one to delete the rows out of the tables.

Looping in stored procedures

Hi

On my site I have a search engine which is used by potential customers to find out what courses we have on offer. Everytime something is written into the search engine the search criteria are logged in a table.

All the courses are held in a different table and I have enabled full text search on this table. Only two cloumns are searched for matches this is the course title and keywords.

What I want to be able to do is write a stored procedure which tells me which search criteria did not match any course titles or keywords. So that I can then assess what word people are using to search for courses and then add any which didn't match any courses to the keywords if they are relevant.

What I have done so far is create a view which contains a distinct list of search criteria. All i need to do now is create a stored procedure which loops through the courses table searching for each distinct search criteria.

I know this is possible to do using asp.net but I would like to do this on the database side to speed the process up a bit.

Any help on this matter would be much appreciated as I seem o be goinbg round and round in circles!!!

Thanking you in advance.

SteveSteve:

You have to use a FETCH statement to loop through a result set in SQL Server. However this is VERY inefficient. It is VERY slow. I would not reccomend it to anyone unless it is absolutly necessary.

On the other hand, you should be able to accomplish the search using a query. If you can post some data from your tables and the struc of the tables then I would be glad to help you write a query to get what you need.

HTH|||Hi Wes

Thanks for the reply and offer to help.

I have got two tables:
tblSTATISTCS and tblFACTFILES

tblSTATISTICS is made up of four columns which are ID(Primary Key), Type (different methods of finding a course i.e. search, courselist ect..), Name (what user typed in or selected) and Date

I have now run a view called queSEARCH which picks out all the distinct names which have 'search' as its type. So in effect tblSTATISTIC ahs gone from

ID Type Name Date
1 Search it 11/11/03
2 Courselist Business 11/11/03
3 Search Art 11/11/03
4 Search Web Design 11/11/03

to... queSEARCH

 Name
 it
 Art
Web Design

So I guess what I need to do now is take each row in queSEARCH and use a CONTAINSTABLE statement for tblFACTFILE in a loop?

tblFACTFILE contains quite a few rows so I have shorted this down to the important ones

Course Code CourseTitle Keywords
10001 Art for beginners Artist, painting, drawing
10002 Computing for beginners Web Design, IT, PC
10003 HND in Computing IT, PC, ASP

I hope this makes sence and thanks again for you offer of help.

Steve|||Steve:

I was following you up until the end. What is the end result that you are looking for? I am sure we can do this in one query rather than a loop.|||Wes

What I want is to find all of the word in queSEARCH which do not match anything in either coursetitle or Keywords. So if the word potatoes was added to the list of words people searched with and it was run against the courses I listed earlier, potatoes would remain in the list. Where as everything else (art, it, web design) wouldn't because it matches at least one of the factfiles.

In effect I would like to produce a page of results to show potential keywords that we are not using already within our factfiles.

I hope this is a little clearer.

Cheers
Steve|||You might be better off constructing a SQL string and sp_executing it.|||I think I am now at the point you were at when you started this thread. Due to the result set that you want it can't be done using a sub query (which I am sure you already knew). I now understand you question perfect, in that you must use a Fetch loop to go through the queSearch table one row at a time and then query to tblFactFile to see if there are any matches. You are going to have to use the CONTAINS clause rather than the CONTAINSTABLE because you can use a variable with the CONTAINS where you can't with the CONTAINSTABLE. Here is the code that you were originally looking for:

DECLARE @.currentName as varchar(50)
DECLARE myCursor CURSOR FOR
SELECT [name] FROM queSearch
--your cursor is now filled with the queSearch results
OPEN myCursor
-- Perform the first fetch.
FETCH NEXT FROM myCursor INTO @.currentName
-- Check @.@.FETCH_STATUS to see if there are any more rows to fetch.
WHILE @.@.FETCH_STATUS = 0
BEGIN
 Select CourseCode FROM tblFactFile WHERE CONTAINS(Keywords,@.currentName) OR CONTAINS(CourseTitle,@.currentName)
 IF @.@.rowcount = 0
 BEGIN
-- IT did not find any matches, therefore do what you need to do
 END
 -- This is executed as long as the previous fetch succeeds.
 FETCH NEXT FROM myCursor INTO @.currentName
END
CLOSE myCursor
DEALLOCATE myCursor
GO

|||Thanks Wes.

I will give this a try tomorrow and let you know how I get on.|||Wes

That peice of code work great. Thanks very much for taking the time to help.|||I am glad to help.

Looping in Stored Procedures

can we write loops in MSSql stored procedures ?

my aim is to do something like

select * from table
while < recordset is not blank >
do something...
next record
end while

is this possible in stored procedure or should i do this from my client
application ?

pl explaintu can very well do this.
for eg:
create procedure looping
as
begin
declare @.l int
set @.l=10
while @.l>1
begin
print @.l
set @.l=@.l-1
end
end

or it can be a condition like
while exists (select * from table)
begin
some code...
end|||thanks for the information

but have one more qn

im SELECTing some rows from a table
and in the loop, i want to perform some operation on each selcted row
after performing the operations , will the loop moves to the next
record ?

pl comment|||no.
for that u need to use cursors.
but use of cursors is not the recomended way of doing things in sql.
so if u could post what u r trying to do, some one out here will surely be help u out.

loop through all stored procedures in database and modify.

I have a need to loop through all stored procedures within a database and modify the procedure if it meets certain criteria. Using .NET I was able to develop a project to loop through the stored procedures, but that's as far as I got. What I need to do is open the stored procedure, search for a certain text string, and change it. Does anybody have a suggestion as to how I can accomplish this? Thanks.

Karen

create the script of all the sp to a single file (which is possible in EM or SSMS) . then replace the string by FIND & REPLACE method .. then run the script in the database, remember to script the permission too...

Madhu

|||

Hmm.

I have two servers with about 100 databases total. Each database uses a single stored procedure that may be different from the ones in the other databases. I wanted to be able to open the stored procedure, search for the string, then modify the text and save the script in an automated way. And I was wondering if SQLDMO can be used through .NET.

|||

You can get the script of this storedprocedure to a file using SQLCMD. but replacing the text u will have to do by .NET program.

read about SQLCMD

SQLCMD -q"SP_HelpText 'Your SP Name'" -o "Outputfile.sql"

Madhu

|||If you are using SQL Server 2000, all of this is available via SQLDMO. You can get a collection of the databases, loop across all of the databases, get a list of procedures, loop across the procedures and extract the definition, then use the string functions in .NET to find and replace. If this is SQL Server 2005, the same capabilities are available with SMO. The samples that ship with SQL Server pretty much have all of the code that you would need.

Looking for table / view that will tell me if I need to reinitialize subscription

I have kind of unique situation. I am running Merge replication. In one of my publications I am only publishing procedures/functions/views. By design, these do not change that often, but when a programmability object changes, it is scripted in a way so that:

1. The article is dropped from the publication

2. the object is then changed

3. The article is added back to the publication

My question is: Is there a table or view that the subscriber or publisher can see that could tell me if reinitialization needs to occur. I am looking at adding an automated script at the subscriber that makes the determiniation and automatically reinitializes the subscription. My alternative is to force the subscriber to reinitialize every time when synchronizing with this publication, even if nothing has changed because the process has to be automated.

Thanks,

Bill

Are you using SQL 2000 and 2005? In SQL 2005, you don't need to drop/recreate articles in order to change the schema. You can directly do ALTER TABLE/VIEW/FUNCTION. For more info, please take a look at BOL http://msdn2.microsoft.com/en-us/library/ms151870.aspx.

Peng

|||I am using 2005, but that will not work, as the publication only contains programmability objects, the underlying base tables are in another publication.|||Not sure what you mean by that will not work. What Peng means is that when you need to change one your progammability article like stored proc or view etc, you can just run alter view or alter proc ... and this DDL action will be replicated when using SQL 2005. so you dont need to drop the article, alter it and readd it. Hence you will not even need to reinitialize your subscriptions.|||When you execute a DDL statement against a view that: is in a publication without any tables in that publication, the query will run but will not complete execution.|||This is a known issue and the workaround is to add a dummy table in that publication.|||When you say create a dummy table, what exactly do you mean? Is this just the "real" table name with one column or something else?|||

Correct, create a non-necessary table that means absolutely nothing. i.e.

create table dbo.t1 (col1 int primary key, col2 int)

Add this as an article to your publication, then your DDL statements should work.

Looking for suggestions

I have two stored procedures (l'll call them P1 & P2). P1, after a lot of processing, creates a temporary table that is used by P2 after an "exec P1" is done. I've separated the logic into two stored procedures because, ultimately, other sprocs will need the output of P1.

I get an error if I use #tempTable as the output table in P1 because it no longer exists after P1 finishes. ##tempTable works, but I'm concerned about concurrency issues. Any suggestions on what construct(s) I should be using?

Thanks in advance!

Global temporary table (GTT) (##tempTable) might not be the one you need

here is the downside of using GTT.

1. you cannot use it to cache Data for a long period of time - let say 1 day

If all the reference to GTT is drop then the data in it is also lost forever

2. GTT is visible to all stored procedure using it - thus concerrency issue

This wont cause you any problem if the data in it is readonly. meaning

You process only once for the entire day. if you'll be cjhanging the content

of GTT from time to time this will cause you headache

two recommendations:

1. if the resultset of P1 will have to live for at least 1 day and there will be no processing required for that span of time,

I would recommend a physical table instead.

2. if the result set of P1 is dynamic and P1 needs to be reused from time to time

I recommend that P1 be transformed into a table-valued function

|||

I totally agree. In fact, I would probably suggest (based on the phrase "lot of processing") that you create a couple of permanent tables. One to hold the results, and the other to keep either:

1. Users of the data that you expect to read the data. As these users read the data, delete their rows, when the last reader finishes, delete them.

2. An amount of time before the results are invalidated. So if you could run it daily, have it be invalidated at midnight, and then users of the data would do their select, and if no data was there, or it was past it's date, it would build the cache, and if the data was there, then fetch it.

If a lot of processing is not really a "lot" then you might use a table-valued function, but there are limitations, like no side effects, if there is any data being written in the process.

Looking for sample code for doing store procedures

I'm looking for any good samples that anyone might be aware of on how to write a SQL Server 2005 stored procedure with error handling (using TRY-CATCH blocks) that I can use as a model.

I did find the article "Detecting and Reporting Errors in Stored Procedures - Part 2: SQL Server 2005 TRY-CATCH Blocks" by Rob Garrison. It's pretty good. The article was written based upon an early beta so the author (understandable) wasn't sure about some possible features. I also was disappointed that the examples didn't flow back to showing how the application handle the resulting errors.

If you know of any good article or samples, please let me know.

TIA,

Richard Rosenheim
Please refer to the INSTAWDB.sql script installed with the samples. It has several SP's which use the new TRY...CATCH syntax.|||

Please take a look at the TRY...CATCH topics in Books Online. They also contains lot of examples.

TRY...CATCH (Transact-SQL)
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/248df62a-7334-4bca-8262-235a28f4b07f.htm

Using TRY...CATCH in Transact-SQL
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/3a5711f5-4f6d-49e8-b1eb-53645181bc40.htm

Looking For Examples Of Stored Procedures in C# or VB.Net.

Hi,

Does anyone have any links to examples of stored procedures written in C# or VB.Net instead of T-Sql. I'd like to see how it looks and works. Thanks! Ken.Hi Ken,

Here are some links for writing stored procedures in .Net compatible language.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/sqlclrguidance.asp
http://www.sqlteam.com/item.asp?ItemID=21927

If you have any concerns please revert back, i will send you the sample code.

Best Regards,
Swamy|||The SQL Server Hands On Labs have examples:

http://msdn.microsoft.com/sql/2005/2005labs/default.aspx

See the SQL CLR lab.|||There are several examples available if you installed the samples and documentation. They are located in the C:\Program Files\Microsoft SQL Server\90\Samples\Engine\Programmability\CLR by default.