Analyzing SQL Server Modules

There might be situations where you find mysterious behaviours of your SQL Server Stored Procedures or Functions probably while deploying a lengthy script with multiple creations or alterations of SPs or Functions. In such cases a DROP statement of one stored procedure could accidentally get included in the CREATE script of another one, possibly if you had forgotten the batch terminator GO. Recently at my workplace we were puzzled to see a stored procedure getting disappeared hours after deployment. After some hazzle, search on the internet, I posted this question on StackOverflow, I was able to get a good suggestion from this guy, to check on tracing the usage of the stored procedure by suggesting the following query on this post.

By querying the sys.sql_modules we'll be able to get the definitions of all stored procedures and functions.

SELECT o.type_desc AS ROUTINE_TYPE
        ,o.[name] AS ROUTINE_NAME
        ,m.definition AS ROUTINE_DEFINITION
FROM sys.sql_modules AS m
INNER JOIN sys.objects AS o
    ON m.object_id = o.object_id
WHERE m.definition LIKE '%search term here%'

The search term could be partial name of the stored procedure in question and the "Definition" field will give the complete script on the creation of that SP or Function.

How to get a column from the last record inserted in SQL

I was in real need for getting a particular column value for the last record inserted. After running through many hints provided in the internet, I came up with the following solution:

Used this forum as a starting point:

http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/8092652e-843e-4064-8a53-4af7237f19ae?prof=required&ppud=4

If there is a column called login in the table tb_admin, to select the value of a particular column of the last row inserted, this is the query :

SELECT login from (select top 1 * from tb_admin order by recid desc) as tempTable

Here "top 1 *" will give the first record and order by recid desc will order it in the descending order to get the last record.