Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Run Sproc on all tables Expand / Collapse
Author
Message
Posted Tuesday, June 4, 2013 12:56 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, September 19, 2014 9:21 AM
Points: 153, Visits: 399
I have a stored procedure that uses a paramater (which is the table name) I am running this sproc on. How can I run it on all my tables in the db I am on?

This is how I would run it:
exec sp_mysproc 'mytablename'

I have about 100 tables and don't want to change the table name in the command 100 times.



Post #1459909
Posted Tuesday, June 4, 2013 1:01 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:49 PM
Points: 12,946, Visits: 12,355
GrassHopper (6/4/2013)
I have a stored procedure that uses a paramater (which is the table name) I am running this sproc on. How can I run it on all my tables in the db I am on?

This is how I would run it:
exec sp_mysproc 'mytablename'

I have about 100 tables and don't want to change the table name in the command 100 times.


You could use the sys tables to help you build a string (which is what I would do) or you could use the undocumented sp_msforeachtable.

I have to say I am a little concerned about a stored proc that receives the table name as a parameter. What does it do?



_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1459912
Posted Tuesday, June 4, 2013 1:12 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, September 19, 2014 9:21 AM
Points: 153, Visits: 399
It runs some stats on the table. The sproc was built by someone else which is no longer with my company.



Post #1459914
Posted Tuesday, June 4, 2013 1:20 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, September 19, 2014 9:21 AM
Points: 153, Visits: 399
This worked for me:

USE FSW_NZ_BIS_Sep12_S;

EXECUTE sp_MSforeachtable 'EXECUTE SP_PRGDS [?];';


Thanks!



Post #1459916
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse