Some times we need to take a print of all the stored procedures, tables or views from our database in SQL Server 2000/2005. For that purpose first we need to list all the user created stored procedures,tables or views. You can make use of the following queries.
1. To List all user created Stored Procedures
Select name From sys.objects Where type = 'P'
2. To List all User created Tables
Select name From sys.objects Where type = 'U'
3. To List all User Created Views
Select name From sys.objects Where type = 'V'
4. You can retrieve data by giving your own filter values, just list all columns to find what is needed
Select * From sys.objects
Now you can create interesting queries for various analysis or just for information purpose
Example:
Get me all the user stored procedures that are created after 20th jan 2009
Select name From sys.objects Where type = 'P' And create_date > '1/20/2009'
So now you can have your own queries...
:)
Note:
In SQL Server Query Analyser (SQL Server 2000) or SQL Server Management Studio Express (SQL Server 2005), you can save the result of the sql query to a file or can view as text.
Right click in the query window and select "Results To". Now you have three options
1) Results to Text, 2) Results to Grid, 3) Results to File
Select your choice.
Subscribe to:
Post Comments (Atom)
2 comments:
Hi Thanks, nice post :) I found one more way to do the same
In SQL Server 2005, you can list the tables and stored procedures using the following statements respectively.
select * from sys.tables
select * from sys.procedures
Note: You need to right click the database name and choose New Query option in Microsoft SQL Server Management Studio Express.
Its Really Good for the beginners.
In SQL 2000 there is no object called sys.objects, its SYSOBJECTS.
So The Query will be like this
1. To List all user created Stored Procedures
Select name From sysobjects Where type = 'P'
2. To List all User created Tables
Select name From sysobjects Where type = 'U'
3. To List all User Created Views
Select name From sysobjects Where type = 'V'
4. You can retrieve data by giving your own filter values, just list all columns to find what is needed
Select * From sysobjects
Thanks Dude
Senthil Kumar
Post a Comment