Run the same TSQL on all databases

See this link for details on how to use sp_msForEachDb


Search an MS-SQL database for a specific named column

Need to find a column in a database? The following SQL will return the tables, and columns, that contain a wildcard value

SELECT AS ColumnName, AS TableName
FROM sys.columns c
JOIN sys.tables t
 ON c.object_id = t.object_id
WHERE LIKE '%column_name%'
ORDER BY TableName,

Transact SQL – (Global) script variables


When running a SQL script using the command sqlcmd tool if you switch context to another database, by using the “use database” command, then any variables defined before this are lost. A similar thing happens if you use the “go” command to group, or batch, commands in the script.

Solution 1:

Create a temporary table, e.g

CREATE TABLE #GlobalVariables (variableOne varchar(30), variableTwo int)
INSERT INTO #GlobalVariables (variableOne, variableTwo) VALUES ('ABCdef', 222222)

Solution 2:

Use script variables

:setvar variableOne "ABCdef"
:setvar variableTwo 222222

To access these in your SQL code you would do the following:

UPDATE #GlobalVariables SET variableOne = '$(variableOne)', variableTwo = $(variableTwo)

Enabling xp_cmdshell in Microsoft SQL Server

By default the sproc is disabled, something to do with a security concern.

To enable the procedure run the following in a query window:
EXEC sp_configure 'xp_cmdshell', 1

If that doesn’t work, and it didn’t for me – I’m running SQL Server 2008 R2. Then run the following commands:
EXEC sp_configure 'show advanced options', 1;
EXEC sp_configure 'xp_cmdshell',1

You can then run something like:
EXEC xp_cmdshell 'iisreset'

How to search a MS-SQL DB for text in a stored procedure (updated)

Looking for a column that’s directly referenced by a stored procedure (sproc) then try running this query:

SELECT SProcName = OBJECT_NAME(object_id), Definition
FROM sys.sql_modules WHERE definition LIKE '%SEARCH_CRITERIA%' 

This replaces the previous post.

If using SQL SSMS 17+ then you need to make sure that the Tools>Options>Query Results>SQL Server>Results to Grid>Retain CR/LF on copy or save is checked.
Otherwise carriage returns are lost.
After checking the option you need to restart SSMS.