Home > Query, SQL > BISQL # 61 – How to analyze Views and Stored Procedures efficiently in Rapid Development !

BISQL # 61 – How to analyze Views and Stored Procedures efficiently in Rapid Development !

October 28, 2011 Leave a comment Go to comments

Hi folks ,

While developing various POC I didn’t have time to go through each and every stored procedure or view because I just have to know what they are doing and how they are getting used

So keeping Rapid Development in my mind following are some useful queries I have come across which are very useful for any SQL developer .

For explanation purpose I have use Adventure Works Database !!

Query 1 : Following query is used to Return a List of the Procedures in a Database

Script :

SELECT s.name AS SchemaName,
p.name AS ProcedureName
FROM sys.procedures AS p
INNER JOIN sys.schemas AS s
ON p.schema_id = s.schema_id
ORDER BY s.name, p.name;

Output :

image

Query 2 : Following Query is used to find Details About Parameters associated with Procedures

Script :

SELECT s.name AS SchemaName, pro.name AS ProcedureName,
        par.name AS ParameterName, t.name AS Datatype,
        par.max_length, par.precision, par.scale,
        par.is_output, par.is_readonly
FROM sys.procedures AS pro
    INNER JOIN sys.schemas AS s
        ON pro.schema_id = s.schema_id
    INNER JOIN sys.parameters AS par
        ON pro.object_id = par.object_id
    INNER JOIN sys.types AS t
        ON par.user_type_id = t.user_type_id
ORDER BY s.name, pro.name, par.name;

Output:

image

Query 3 :Following query is used to views for Information About  a View form a given database

Script :

SELECT    s.name AS SchemaName,
        v.name AS ViewName, c.name AS ColumnName,
        t.name AS Datatype, c.max_length, c.precision,
        c.scale
FROM sys.views AS v
    INNER JOIN sys.schemas AS s
        ON v.schema_id = s.schema_id
    INNER JOIN sys.columns AS c
        ON v.object_id = c.object_id
    INNER JOIN sys.types AS t
        ON c.user_type_id = t.user_type_id
ORDER BY s.name, v.name, c.column_id;

Output :

image

Hope this three query is useful for you !!

Thanks for visiting my blog !!

If you really like reading my blog and understood at lest few thing then please don’t forget to subscribe my blog .

If you wan daily link and analysis or interesting link go to following website which will give @ your inbox please subscribe our following link resource blog

Where todays links are

Link Resource Website

Advertisement
Categories: Query, SQL
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

Gravatar
WordPress.com Logo

You are commenting using your WordPress.com account. ( Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. ( Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. ( Log Out /  Change )

Cancel

Connecting to %s

%d bloggers like this: