[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

SQL Server 2008 - Stored Procedure break down

Posted on 2014-04-10
4
Medium Priority
?
1,135 Views
Last Modified: 2014-04-14
Hi and thanks,

I am very new to SQL Server...

I have 3 questions:

Were is sp_executesql located within the SQL Server 2008???
Like Stored Procedures are in DB > DB Name > Programmability > Stored Procedures
Just have trouble finding this System SP

It is also mentioned that (see attachments) there is a "DATABASE_TABLES"  but I can not find were it is being used within the SP's??? Maybe I just don't know were to look...


Also how do you find which tables are linked to a View???


Thanks
Proclaim-Purge-By-FileId.docx
sys-sp-updatestats.docx
0
Comment
Question by:Amour22015
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 27

Expert Comment

by:Zberteoc
ID: 39992190
all system stored procedures are located in the master database or in the msdb database if they are jobs related. Particularly they all start with sp_ prefix, which should never be used by the users.

It is not recommandable to try to modify them, actually starting 2008 you can't. You can always see the code for a procedure by running:

sp_helptext spname

but it will work only for SQL procedures and not the CLR or extended ones.
0
 

Author Comment

by:Amour22015
ID: 39992418
so there for, no one would be able to change sp_executesql to include "DATABASE_TABLES" .

With that said then how come I can not find "DATABASE_TABLES"  in the SP's attached???

No were do I see: "DATABASE_TABLES" yet someone mentioned:
DATABASE_TABLES
that will be the table that tells what dat is Ready for PURGE
if it is ready for purge the Status will be set to PURGE_ELIG

maybe I just don't know were to look??? Is there another way???

Also how do you find which tables are linked to a View???

Thanks
0
 
LVL 27

Accepted Solution

by:
Zberteoc earned 1000 total points
ID: 39992449
If you want to modify the functionality you would have to generate the code as I mentioned above, however for this particularly sp you can't do it, and then you can create a new procedure in one of your databases but make sure you change its name. This way you can modify it as you want.

The system stored procedure are usually located under the databse node > Programability > System Stored Procedures. Those you can modify as I said by changing the name and create them as user stored procedures. The sp_executesql is an internal/extended sp so you can't modify it.

I am not sure what you mean by DATABASE_TABLES, I don't know about such thing in SQL server but you can see the tables in either:

select * from sys.tables

or

select * from INFORMATION_SCHEMA.TABLES

INFORMATION_SCHEMA is a schema that contains a lots of useful views that have the information about database objects ready compiled for you, like table, columns, routines(procedures and functions, views, constraints, etc,

Here more info:
http://technet.microsoft.com/en-us/library/ms186778.aspx
.
0
 

Author Closing Comment

by:Amour22015
ID: 39998920
Great
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

649 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question