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 MS SQL Server Management Studio 17+ then you need to make sure that the following option is checked:

Tools>Options>Query Results>SQL Server>Results to Grid>Retain CR/LF on copy or save

Otherwise carriage returns are lost. After checking the option you need to restart SSMS, for it to take effect on any queries.

Your WordPress admin account suddenly not recognised?

Sound familiar? For some reason I couldn’t log into my admin account, the password was always wrong. I’ve still no idea how this happened but fortunatley if you get to your MySQL DB, either via SQL tools or MyPhpAdmin, there is a way out!
Just run this query against the DB and hey presto it’s all sorted for you.
UPDATE `wp_users` SET `user_pass` = MD5( 'new_password' ) WHERE `wp_users`.`user_login` = "admin_username";
But still none the wiser as to how it happened in the first place. The account doesn’t seem to have been hacked as nothing else has changed.