By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,369 Members | 1,147 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,369 IT Pros & Developers. It's quick & easy.

SQL how to view the list of tables in DB, the columns in the table, stored procedure

P: 16
How do i view the code in stored procedure?what permission should I have for it.

Also how do I get the list of tables in the DB?
Feb 19 '07 #1
Share this Question
Share on Google+
13 Replies


dorinbogdan
Expert 100+
P: 839
1.
Expand|Select|Wrap|Line Numbers
  1. select * from information_schema.tables where table_type = 'base table' 
2.
Expand|Select|Wrap|Line Numbers
  1. sp_helptext 'yourStoredProcedureName' 
Feb 20 '07 #2

P: 2
1.
Expand|Select|Wrap|Line Numbers
  1. select * from information_schema.tables where table_type = 'base table' 
2.
Expand|Select|Wrap|Line Numbers
  1. sp_helptext 'yourStoredProcedureName' 
3. You can use the sysobjects to get all these information.
select * from sysobjects where type='u' will tell all the tables in the db.
for stored procedure u can use
select * from sysobjects where type='p'
for column related infor get the required from syscolumns table
Feb 21 '07 #3

P: 16
Hi all,

Thanks

select * from information_schema.tables where table_type = 'base table'

select * from sysobjects where type='p'
the above query worked

Thanks a lot

But

I tried sp_helptext 'spname'

But it returned

Server: Msg 15009, Level 16, State 1, Procedure sp_helptext, Line 54
The object 'spname' does not exist in database 'autodb' or is invalid for this operation.
But this sp is there when I run the below query
select * from sysobjects where type='p'

I have only Read permissions in the DB... may be because of this I am not able to view it?

How do I find my permission in the DB?

Thanks a lot for your help
Feb 21 '07 #4

dorinbogdan
Expert 100+
P: 839
Per SQL Help, you need:
Execute permissions default to the public role.
Feb 21 '07 #5

P: 16
How do I find if I have permissions to execute it?
Feb 21 '07 #6

dorinbogdan
Expert 100+
P: 839
I think that it is better to contact the database administrator to check and set any permission settings.
Feb 22 '07 #7

P: 17
How do I find if I have permissions to execute it?
You can use like this
-- grant select permission on the procedure_name to the public role
GRANT EXECUTE ON procedure_name TO public
Feb 22 '07 #8

dorinbogdan
Expert 100+
P: 839
NOTE: execute sp_helptext in the database in which the procedure exists

Stored procedures created with the ENCRYPTION option cannot be viewed with sp_helptext.
Feb 22 '07 #9

dorinbogdan
Expert 100+
P: 839
A new method available in SQL Server 2005 is using OBJECT_DEFINITION:

The following example returns the definition of the system stored procedure sys.sp_columns:
Expand|Select|Wrap|Line Numbers
  1. SELECT OBJECT_DEFINITION (OBJECT_ID(N'sys.sp_columns')) AS [Object Definition]
Feb 22 '07 #10

P: 16
You can use like this
-- grant select permission on the procedure_name to the public role
GRANT EXECUTE ON procedure_name TO public

I tried this but it didnt work as it said that I dont have permission or the it doesnt exsist :(

I dont have execute permission...
I dont want to execute the stored procedure, but want to view the code in it

I cant ask the DBA... as I dont know who it is...
Feb 23 '07 #11

dorinbogdan
Expert 100+
P: 839
If have SQL Enterprise Manager you could see the sp code :
Open Enterprise Manager and go to YourSQLServer/Databases/YourDB/Stored Procedures/ right-click on your sp and select Properties...
God bless you.
Feb 26 '07 #12

P: 16
I dont have Enterprise Manager because It is now upgraded to SQL2005 so I need t owait until SQL 2005 Server is installed on my PC.

I'll try this once I get it installed and post a message again on this :)

Thanks a lot for all your response.
Feb 26 '07 #13

dorinbogdan
Expert 100+
P: 839
Ok, let me know when ready.
Feb 26 '07 #14

Post your reply

Sign in to post your reply or Sign up for a free account.