It might be already known to you but I thought for sharing because I frequently use this command and it is very useful command. When someone wants to determine detail of a function or stored procedure he/she can use this useful command. The syntax is very simple. Just write sp_helptext Storedprocedure/ functionname For example …
When you do SQL Server maintenance one of important aspect is available space on server drive because your SQL SERVER data is dependent on space . Now what you need to do to get free space from each drive ? Just create a simple job which run on daily basis which send you space …
Sometimes it may require that you don’t know what will be output of a stored procedure ? what kind of result set it return ? In such case SQL SERVER provided a new DMV statement which is sys.dm_exec_describe_first_result_set_for_object. In other words if we want to know result set’s meta data then we can use …
This is one of the interesting feature which I like most. Instead of passing values from collection one by one pass entire collection to stored procedure as a table value parameter. I know above statement is not digestive enough so lets understand this by an example. Suppose , I have a table tblStudent with …
Problem:- Sometimes it happened that you need to run a single statement on entire tables which exists in database. so most of the time we think of cursor which run for each sys.objects (table) and we execute the dynamic statement by replacing table name. Solution: Although the solution we are thinking is correct there is …
Sometimes , We need to know what is the structure of stored procedure ? , How many columns will be return ? So to determine this we have easy stored procedure provided by SQL Server which is sp_describe_First_Result_Set. Below is the template to run it. You can provide your stored procedure name as variable …
Sometimes for security reason it is require to encrypt the objects which you have created (Suppose you have deployed your database on client end or shared hosting and you don’t want any other person to see your logic behind your stored procedure and function). To achieve this you need to use a simple keyword …
To determine table used in stored procedure or table reference We need to run following commandGoSELECT object_Name(Id)as objectName,text FROM syscomments WHERE text like ‘%tblUser%’GoWe can use following command as wellGoSELECT ROUTINE_NAME,ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_DEFINITION like ‘%tblUser%’ GO In the above queries we have used tblUser as table name we can replace it and add any …
To get all stored procedures from database then we can use following commandGOSELECT name,type_Desc,create_Date,Modify_DateFROM sys.procedures WHERE type=’p’AND type_desc =’SQL_STORED_PROCEDURE’GO Or we can use following commandGOSELECT ROUTINE_NAME,ROUTINE_TYPE,ROUTINE_DEFINITION,CREATED,LAST_ALTEREDFROM INFORMATION_SCHEMA.ROUTINESWHERE ROUTINE_TYPE =’PROCEDURE’ Enjoy !!!