Script to iterate through all tables in a database

[Bulldog13]

Does anyone know of a useful script or utility to iterate through all of the tables in a given database to spit out all of its dependencies? And then even possibly pull more information out of the stored procedures as to their inputs and outputs? I am inheriting a large project with a code handoff looming this week and need some type of way to guarantee that I catch all of the information. Something like right clicking on a table, then "View Dependencies", but progmattic (lots of tables) and hopefully more robust.



Cheers !

 

[Crusty]

What RDBMS?

 

[Bulldog13]

SQL Server 2005.

I found this article How to: Script Out the Dependencies for a Database in Visual Basic .NET which looks promising, but I am not sure where to add the credentials (ip of server, un, pw) so that I can actually connect to the server.

 

[Crusty]

'Connect to the local, default instance of SQL Server.
Dim srv As Server
srv = New Server
'Reference the AdventureWorks database.
Dim db As Database
db = srv.Databases("AdventureWorks")
'Define a Scripter object and set the required scripting options.


That's the connection information, you would have to replace that code with one that connects to your server/database. The default constructor connects to a localhost server with default credentials.

 

[Bulldog13]

USE [MYDB]
GO

DECLARE @return_value int

EXEC @return_value = [dbo].[sp_depends] TABLE1

SELECT 'Return Value' = @return_value

GO


I found this which seems to work well, how do I modify it to iterate through all the tables in a given database instead of doing it manually 1 by 1 ?

 

[KLin]

http://msdn.microsoft.com/en-us/library/ms180169.aspx

define a cursor to a list of tables, set a string variable to cursor value, run SP_depends on variable, get next cursor value, set string variacle, rinse, repeat.

 

[Bulldog13]

Originally posted by: KLin
http://msdn.microsoft.com/en-us/library/ms180169.aspx

define a cursor to a list of tables, set a string variable to cursor value, run SP_depends on variable, get next cursor value, set string variacle, rinse, repeat.

My T-SQL skills are very weak...care to share the code =)

 

[KLin]

Originally posted by: Bulldog13

Originally posted by: KLin
http://msdn.microsoft.com/en-us/library/ms180169.aspx

define a cursor to a list of tables, set a string variable to cursor value, run SP_depends on variable, get next cursor value, set string variacle, rinse, repeat.

My T-SQL skills are very weak...care to share the code =)

I recommend doing some searched on http://groups.google.com.

You can find a lot of code examples there for everything t-sql.