How do I access sql system stored procs from code

Hello:

Okay not really understanding how to do this, but how do I access system stored procedures from code? Basically I would like to determine information about each table in my database, the primary key, number of columns, the names of the columns and the datatypes of the columns in each table. Not too much to ask. How do I go about accessing this information. I have a fairly good idea on how to do it using T-SQL but how do I do it using an assembly? Has anyone else done this before? Any help would be greatly appreciated!

ThanksOkay i am stupid again and did not do a google search. So after do this I found an excellent resource for this particular problem. I found it at:

http://www.ftponline.com/vsm/2003_01/magazine/columns/databasedesign/default.aspx

Shows how to access Table Metadata programmatically using C# and VB.Net AWESOME! Working on my own code generator and now I am finally getting to a point where I can start to finish it. All I needed was a way to generate stored procs from table metadata. Now I have the table metadata and a way to generate stored procs I am all set. AWESOME.

Thanks for those that read this post hope it helps you out in the future. Remeber a real programmer does not write code but writes code that generates code for him/her.|||Just the same as any other stored procedure...you need permissions to run the proc and the path to the proc, e.g master

How do I access a SQL view in VB.Net?

I'm able to access my stored procedures just fine, but I can't query/show a view that I'd built in SQL Server. Any suggestions on how would be welcome.

Sample Stored Procedure reference, and I just want to reference a view now:

 ' Connect to the Database
Dim MyConn As SqlConnection = New SqlConnection(ConfigurationSettings.AppSettings("myConnectionString"))
' Establish the set of data commands and a database connection that are used to fill the dataset
Dim MyCommand As New SqlCommand("vw_Mast_PW_Accounts", MyConn)
MyCommand.CommandType = CommandType.StoredProcedure
MyConn.Open()
dgMyGrid.DataSource = MyCommand.ExecuteReader
dgMyGrid.DataBind()
MyConn.Close()

I might be mistaken here, but a view is treated pretty much like a table. So if you want to return data from a view, query against it as if it were a table: SELECT * FROM vw_myView WHERE ...

You'll need to make sure your web app has permissions to query the view and, I think, any underlying tables (though I might be wrong on that last point).|||That's great, but do is there a way to do it without using SQL but simply referencing it as I would a stored procedure (i.e., just the name)?

Thank you,
Brent|||I don't believe so, for that same reason that you couldn't do that with a table.

Youcould create a stored procedure that returned data from your view, if you really needed that functionality...|||I gave up and built the stored procedure to access that view. Thank you for the suggestion!

Brent

How can you use VSS to manage stored procedures

Can anyone explain how to use the VSS integration in SSMS to mange stored
procedures? We use VSS to manage source code in Visual Studio but I can't
figure out how this is done with stored procedures in SMSS
What I do is:
1) Create a Solution for stored procedures.
2) Add the solution to Source Control
3) Add a new query.
4) Select a stored procedure
5) Script the SP as CREATE to clipboard
6) Paste into the new query
7) Add extra code at the bottom to grant permissions
8) Rename NewQuery.sql to my_SP.sql in the solution explorer
9) Check in pending check ins
10) repeat steps 3 to 9 until you have all your SP's under Source Control
I always script an existing sp as create so that I can't accidentally hit F5
You can do things like add a separate project to the solution for each
database or a separate solution for each database or any other way that
suits your way of organising things.
Regards,
Nigel Ainscoe MBCS MCP
"D. Haber" <DHaber@.discussions.microsoft.com> wrote in message
news:7BFAB085-3D2F-4FB0-9822-BE9BDEC2DB52@.microsoft.com...
> Can anyone explain how to use the VSS integration in SSMS to mange stored
> procedures? We use VSS to manage source code in Visual Studio but I can't
> figure out how this is done with stored procedures in SMSS
>
>

How can you mass-validate stored procedures, views, etc?

I am looking for a way to validate all objects in the database that is is
possible for in some automated fashion. For instance, if a column is droppe
d
from a table stored procedures that rely on it will fail on the next
execution, but I want to be able to run a process that will reveal that prio
r
to execution. I would want to do that for views and user defined functions
as well. Other than executing every procedure and function and selecting
from every view does anyone know of a way to do this?
I thought I'd just script the objects out for ALTER and run that script
against the database but it appears you can only create an ALTER script one
object at a time through EM and with thousands of objects I really don't wan
t
to go through that.
I created a script that ran sp_recompile against every stored procedure,
then another block that executed every stored procedure without supplying an
y
parameters and that seemed to catch stored procedures that relied on missing
columns. The problem with that is it makes the assumption that the stored
procedure would either fail when no parameters were supplied, or would be
harmless when the stored procedure executed either because it required no
parameters or had defaults for all parameters. Of course this also only
worked for stored procedures and not the functions and views.
I made the mistake of changing a view without realizing that it was used in
a stored procedure and when that stored procedure executed it failed. You
can't depend on the dependencies that SQL maintains since they are not alway
s
correct, so you can't even make note of the dependent objects and selectivel
y
check only those objects without risking missing a dependency.
Any suggestions would be greatly appreciated.Byron (Byron@.discussions.microsoft.com) writes:
> I am looking for a way to validate all objects in the database that is
> is possible for in some automated fashion. For instance, if a column is
> dropped from a table stored procedures that rely on it will fail on the
> next execution, but I want to be able to run a process that will reveal
> that prior to execution. I would want to do that for views and user
> defined functions as well. Other than executing every procedure and
> function and selecting from every view does anyone know of a way to do
> this?
> I thought I'd just script the objects out for ALTER and run that script
> against the database but it appears you can only create an ALTER script
> one object at a time through EM and with thousands of objects I really
> don't want to go through that.
>...
This is indeed a difficult situation, and I don't really think there
is a single good way around it.
The method in the above paragraph is fairly easy to apply: use Generate
Scripts from Enterprise Manager to script the entire database. Then
open the generated script in a an editor and change CREATE PROCEDURE
to ALTER PROCEDURE.
However, you may not catch all errors this way. Say that you have:
INSERT #temp (...)
SELECT ..., missing_column
FROM ...
Because of deferred name resolution, SQL Server will not alert you of the
missing column. It sees that #temp is missing, and will defer compilation
of that statement until #temp has been created, and thus the error will
not occur until run-time.
The approach I use myself is based on the fact that we have all code
under version control. We also have a build tool that goes head over
heels to nullify the effect of deferred name resolution, by creating
all temp tables in an SP before loading the procedure. Thus a missing
column is noticed directly. Furthermore, it's easy for me to build an
empty database from SourceSafe, which will give me a complete sysdepends
that I can rely on.

> I created a script that ran sp_recompile against every stored procedure,
> then another block that executed every stored procedure without
> supplying any parameters and that seemed to catch stored procedures that
> relied on missing columns. The problem with that is it makes the
> assumption that the stored procedure would either fail when no
> parameters were supplied, or would be harmless when the stored procedure
> executed either because it required no parameters or had defaults for
> all parameters. Of course this also only worked for stored procedures
> and not the functions and views.
Our build-and-load tool is available for free download on
http://www.abaris.se/abaperls/. To get started with it, may be overmuch.
However, the toolset in includes a free-standing tool SPTRITEST which
performs essentially the above, but also provides dummy value for all
parameters, so it permits you test far more procedures. It goes without
saying that you should run this on a *copy* of the database, or else
you get a load of junk in it.
And it may still not find all cases of missing columns, since if there
are IF-ELSE branches, you are likely to pass over only one of them.
I also need to add the disclaimer that I wrote it in 6.5 days, and I
have not used it myself for ages.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

How can we understand ?

How can we understand
- which stored procedures executes slowly ,
- which stored procedures needs more memory ,
- which indexes has much more mb
- which indexes are not used
- which indexes stay in memory
thanks for your answersUse Profiler
Madhivanan

How can I tell which SP is in error when trying to Export?

I am trying to Export a database to another using the export wizard. It
states that it found an invalid column name on one of the stored procedures
but doesn't say WHICH strored procedure, which is problematic since there
are nearly a thousand of them.
Does anyone know how to tell which sp is in error?"Tom" <nfr@.nospam.com> wrote in message
news:uyu4zBFOEHA.204@.TK2MSFTNGP10.phx.gbl...
> I am trying to Export a database to another using the export wizard. It
> states that it found an invalid column name on one of the stored
procedures
> but doesn't say WHICH strored procedure, which is problematic since there
> are nearly a thousand of them.
> Does anyone know how to tell which sp is in error?
There must be Details button I think...
Else it is safe to generate script of all of your procedures (Select them in
EM - all tasks -> generate script) and run it on same database from Query
Analyzer - you will see what is the exact error and where.
Bojidar Alexandrov