BISQL # 61 – How to analyze Views and Stored Procedures efficiently in Rapid Development !
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
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;
Query 2 : Following Query is used to find Details About Parameters associated with Procedures
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;
Query 3 :Following query is used to views for Information About a View form a given database
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;
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