473,320 Members | 1,865 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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

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
13 17654
dorinbogdan
839 Expert 512MB
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
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
Bangaru
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
839 Expert 512MB
Per SQL Help, you need:
Execute permissions default to the public role.
Feb 21 '07 #5
Bangaru
16
How do I find if I have permissions to execute it?
Feb 21 '07 #6
dorinbogdan
839 Expert 512MB
I think that it is better to contact the database administrator to check and set any permission settings.
Feb 22 '07 #7
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
839 Expert 512MB
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
839 Expert 512MB
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
Bangaru
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
839 Expert 512MB
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
Bangaru
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
839 Expert 512MB
Ok, let me know when ready.
Feb 26 '07 #14

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

Similar topics

8
by: tom | last post by:
I am new to SQL administration. >From a list of IDs that are the primary key in one table (i.e. Customer Table), I want to make changes in tables that use those IDs as a foreign key. ...
10
by: serge | last post by:
Using "SELECT * " is a bad practice even when using a VIEW instead of a table? I have some stored procedures that are identical with the difference of one statement in the WHERE clause. If I...
2
by: Justin | last post by:
I am creating a web app for a client in VS.NET using ASP.NET with C#. I need to query three tables in a database using one parameter and display the results on the page. my question is should I use...
104
by: Beowulf | last post by:
I have the view below and if I use vwRouteReference as the rowsource for a combo box in an MS Access form or run "SELECT * FROM vwRouteReference" in SQL Query Analyzer, the rows don't come through...
2
by: Joe Campbell | last post by:
I have a DBA that wrote a stored procedure that does a SELECT from a particluar SQL Server table. Within that stored procedure he links over to grab a column from another database table. I need to...
3
by: pinney.colton | last post by:
I would like to create a stored procedure which creates a temp table to store some XML. The # of fields of XML is dependent upon the contents of another table in the application, so the first part...
5
by: jonceramic | last post by:
Hi All, I started developing in Access, and people took notice and so we're starting to migrate into our corporate's bigger Oracle system. I'll still be using my developed Access front ends,...
9
by: Ots | last post by:
I'm using SQL 2000, which is integrated with a VB.NET 2003 app. I have an Audit trigger that logs changes to tables. I want to apply this trigger to many different tables. It's the same trigger,...
12
by: nyathancha | last post by:
Hi, I have a question regarding best practices in database design. In a relational database, is it wise/necessary to sometimes create tables that are not related to other tables through a...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.