Useful System level tables or Stored Procedure

Finding a  particular table is being used in the stored procedure

 --Searching for Empoloyee tableSELECT NameFROM sys.proceduresWHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%Employee%'



--Searching for Empoloyee table and RateChangeDate column togetherSELECT NameFROM sys.proceduresWHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%Employee%'AND OBJECT_DEFINITION(OBJECT_ID) LIKE '%RateChangeDate%

Finding the parameter list of a stored procedure or table

Sp_help '<sql server object name>'

(or)

Select 
A.NAME,B.Name,A.length,A.iscomputed 
From 
Syscolumns A,Systypes B
where id = 
(select id from sysobjects where name='<sql server object name>')
and A.XUSERTYPE=B.XUSERTYPE

Finding the stored procedure text or table column list

Sp_helptext <Object Name/TableName> 

Retrieving the column name and the datatype in sql server

SELECT column_name 'Column Name',
data_type 'Data Type',
CHARacter_maximum_length 'Maximum Length'
FROM information_schema.columns
WHERE table_name = 'liclicense'


Returning the number of rows in a table without COUNT

SET NOCOUNT ON 
DECLARE @recCount int

SELECT   @recCount = rows 
FROM   
sysindexes
WITH (NOLOCK) 
WHERE    id = OBJECT_ID('DSAApplication')  AND indid < 2

print CAST(@recCount AS VARCHAR(20))