Get stored procedure count in sql server

Thursday

Some Important  SQL Queries:

                                     The following SQL queries  are very much used to my one of  a stored procedure based .net project. It may useful you too.

Get stored procedure count from a sql table:

SELECT count(*) SPCOUNT FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE='PROCEDURE'

Get number of function from a sql table:

SELECT count(*) FUNCTIONCOUNT FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE='FUNCTION'

Get all stored procedure name from a sql table:

SELECT name AS spname
FROM sysobjects
WHERE (xtype = 'p') AND (name NOT LIKE dt%')
ORDER BY name 

Get all table name in a sql database:

Select table_name from information_schema.tables
where table_type='Base table' order by table_name 

Get stored procedure(s) for a sql table:

SELECT DISTINCT so.name
FROM syscomments sc
INNER JOIN sysobjects so ON sc.id=so.id
WHERE sc.TEXT LIKE '%table name%'