Trying to write a stored procedure

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

SQL Server 2000 Forums

SQL Server Development (2000)

Trying to write a stored procedure

Author

Topic

San2000
Starting Member

1 Post

Posted - 2008-02-14 : 12:21:51

Hi Guys,

Can you help me out how to write a stored procedure for the below requirement.
I am frontend developer and new to SQL Server 2000.

Requirement:
Select all the databases from master database.(say there are 10 d1,d2,d3,d4..d10)
if the database has tables like t1 and t2
Select tables from the database loop.
find for specific value say test in the column C1 from the tables t1,t2
if found
write the data into a flat with the databasename,tablename,columnname and columnvalue for rollback
Update the value from test to test123

i am able to write some queries to solve the problem but i am not sure how to club all these queries to write a stored procedure or SQL statements.

Step1:

select name from master.dbo.sysdatabases order by name (it results 10 databases say d1,d2..d10)

Step2:
Check for the tables t1,t2 in all the databases d1..d10
select sysobjects.name as table_name, syscolumns.name as field_name
from sysobjects, syscolumns
where sysobjects.name in ('t1,'t2' ) and syscolumns.name = 'C1'
and syscolumns.id = sysobjects.id

T1,T2 are table names and c1 is the column name

Step3:
Check for value 'TEST' in the tables t1,t2 (again loop)

SELECT * FROM table_name WHERE field_name='test'

if found write the output into a flat file ex:databasename|tablename|fieldname|fieldValue ----this is for rollback

Now update the value from 'TEST' to 'TEST123'

Thanks
San

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-17 : 08:55:53

Put the initial queries result into a temp table. loop through the records of this to check for tables t1,t2 and the column name C1 and write an if condition such that if found and data is test, write the reqd data as o/p to file. refer this link to write records to file

[url]http://www.sqlservercentral.com/articles/Miscellaneous/writingtoafileusingthesp_oacreatestoredprocedurean/1694/[/url]

also do an update to change test to test123

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-02-18 : 03:30:03

See if you are looking for something like this
http://vyaskn.tripod.com/search_all_columns_in_all_tables.htm

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources