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

[Bangaru]

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?

[dorinbogdan]

1.

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

2.

sp_helptext 'yourStoredProcedureName'

[BalajiJaganathan]

1.

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

2.

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

[Bangaru]

Hi all,

Thanks

select * from information_sch ema.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

[dorinbogdan]

Per SQL Help, you need:
Execute permissions default to the public role.

[Bangaru]

How do I find if I have permissions to execute it?

[dorinbogdan]

I think that it is better to contact the database administrator to check and set any permission settings.

[Akhilesh1505]

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

[dorinbogdan]

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.

[dorinbogdan]

A new method available in SQL Server 2005 is using OBJECT_DEFINITI ON:

The following example returns the definition of the system stored procedure sys.sp_columns:

SELECT OBJECT_DEFINITION (OBJECT_ID(N'sys.sp_columns')) AS [Object Definition]

[Bangaru]

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...

[dorinbogdan]

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.

[Bangaru]

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.

[dorinbogdan]

Ok, let me know when ready.