Get query from SPID

When number of queries are executing in SQL Server, we need some code to get that query from SPID which is taking time to execute

First get the SPID which is creating problem, then execte the following code for that SPID.

DECLARE @sqltext VARBINARY(128)

SELECT @sqltext = sql_handle FROM sys.sysprocesses

WHERE spid = 61

SELECT TEXT FROM sys.dm_exec_sql_text(@sqltext)

GO

Here, 61 is SPID. This will return the query being executed at this SPID.

To execute this code requires VIEW SERVER STATE permission on the server.

With this permission on DB Server, User can Read Dynamic Management Views and Functions

Dynamic management views and functions return server state information that can be used to monitor the health of a server instance, diagnose problems, and tune performance.

References:

http://technet.microsoft.com/en-us/library/ms181929(v=sql.110).aspx

Search string in all DB Objects in all databases

Sometimes we need to search for a string in table names, Column Names, View definitions or Procedure definitions. By executing these queries one can safely replace the string in all of its dependent Objects.

SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE ‘%MyData%’

SELECT * FROM INFORMATION_SCHEMA.VIEWS WHERE VIEW_DEFINITION LIKE ‘%MyData%’

SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME LIKE ‘%MyData%’

SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_DEFINITION LIKE ‘%MtData%’

The above queries will search the string in all the Databases on a Server.