SQL Query to list stored procedure inner objects

SQL Server Query to list stored procedure inner objects:

Please mention Actual procedure/Funtion/View Name in Place of 'StoredProcedureName'

SELECT Object_name(referencing_id) ParentObject,
referenced_entity_name InnerObject,
type_desc,
create_date,
modify_date
FROM sys.sql_expression_dependencies d
INNER JOIN sys.objects o
ON d.referenced_entity_name = o.name
WHERE Object_name(referencing_id) = 'StoredProcedureName'

Thanks,
Dinesh Babu

SQL Query to get the lines of Code of stored procedure in MS Sql Server

Query to get the lines of code of sql objects in ms sql server
Please Enter object Name in place of 'StoredProcedureName'

SELECT t.sp_name,
Sum(t.lines_of_code) - 1 AS lines_ofcode,
t.type_desc
FROM (SELECT o.name AS sp_name,
( Len(c.text) - Len(Replace(c.text, Char(10), '')) ) AS
lines_of_code,
CASE
WHEN o.xtype = 'P' THEN 'Stored Procedure'
WHEN o.xtype IN ( 'FN', 'IF', 'TF' ) THEN 'Function'
END AS type_desc
FROM sysobjects o
INNER JOIN syscomments c
ON c.id = o.id
WHERE o.xtype IN ( 'P', 'FN', 'IF', 'TF' )
AND o.category = 0
AND o.name IN ( 'StoredProcedureName' )) t
GROUP BY t.sp_name,
t.type_desc
ORDER BY 1

Thanks,
Dinesh Babu

What is Algebrizer

What is Algebrizer(in SQL 2000 Normalizer in 2005/8 Algebrizer) Algebrizer is a process in query execution, which starts its work after Parser. Once the parser finds query syntax correct, it passes it to the algebrizer. Here start work of algebrizer: • Algebrizer is responsible for objects and columns names (which you have provided in query or which are being referenced by your query) verification. For example if wrong name for a table is typed in a query, then its algebrizer’s duty to verify and generate an error. • Algebrizer also identifies all data types which are being processed for a given query. • Algebrizer verify that GROUP BY and aggregate columns are placed in right place or not. For example if you write following query and just Ctrl+F5 (just to parse). No error will be generated. But when you will press F5 (execute it). After Parsing, algebrizer will work and return an error. sys.dm_exec_query_optimizer_info Is DMV which provide the query optimizer information A. Viewing statistics on optimizer execution What are the current optimizer execution statistics for this instance of SQL Server? SELECT * FROM sys.dm_exec_query_optimizer_info; B. Viewing the total number of optimizations How many optimizations are performed? SELECT occurrence AS Optimizations FROM sys.dm_exec_query_optimizer_info WHERE counter = 'optimizations'; C. Average elapsed time per optimization What is the average elapsed time per optimization? SELECT ISNULL(value,0.0) AS ElapsedTimePerOptimization FROM sys.dm_exec_query_optimizer_info WHERE counter = 'elapsed time'; D. Fraction of optimizations that involve subqueries What fraction of optimized queries contained a subquery? SELECT (SELECT CAST (occurrence AS float) FROM sys.dm_exec_query_optimizer_info WHERE counter = 'contains subquery') / (SELECT CAST (occurrence AS float) FROM sys.dm_exec_query_optimizer_info WHERE counter = 'optimizations') AS ContainsSubqueryFraction;