MS SQL Tips & Tricks

How to get the body of stored procedure or view using sql query

To get the body of stored procedure using sql query you can use something like that. The same way you can access the body of a view.

SELECT OBJECT_DEFINITION(OBJECT_ID('procedure_name_here'))

or

SELECT OBJECT_DEFINITION(OBJECT_ID('view_name_here'))

to get the list of table's columns you can use query:

select it.TABLE_NAME, 
	   ic.COLUMN_NAME, 
	   ic.DATA_TYPE, 
	   isNull(CHARACTER_MAXIMUM_LENGTH,0 ) as SIZE
from INFORMATION_SCHEMA.TABLES it
inner join INFORMATION_SCHEMA.COLUMNS ic on 
	(it.TABLE_NAME = ic.TABLE_NAME) 
where it.TABLE_NAME='Employees'
order by it.TABLE_NAME

Learn more tricks

Add Comment

Comments