Run the same TSQL on all databases

See this link for details on how to use sp_msForEachDb
An example, that retrieves all view names on the current server follows:

DECLARE @command varchar(100)
SELECT @command = 'USE ?; SELECT name FROM sysobjects WHERE type = ''V'' ORDER BY name'
EXEC sp_MSforeachdb @command

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 c.name AS ColumnName,
       t.name AS TableName
FROM sys.columns c
JOIN sys.tables t
 ON c.object_id = t.object_id
WHERE c.name LIKE '%column_name%'
ORDER BY TableName,
         ColumnName;

T-SQL Removing DB constraints

Quick post, aide-memoire, for disabling and then re-instating database contraints in MS-SQL.

EXEC sp_MSforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT ALL"

EXEC sp_MSforeachtable @command1="PRINT '?'", @command2="ALTER TABLE ? WITH CHECK CHECK CONSTRAINT ALL"

Transact SQL – (Global) script variables

Problem:

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)

Manipulating XML in MS-SQL using XQuery – a beginners guide

A link to a blog that can be used as the starting point for those who need to manipulate XML held in MS-SQL.
http://beyondrelational.com

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
Reconfigure


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;
Reconfigure;
GO
EXEC sp_configure 'xp_cmdshell',1
Reconfigure
GO


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


Getting the keys for a table in Transact-SQL

To get the keys for a table in Microsoft SQL Server use the stored procedure: sp_pkeys

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%' 
ORDER BY SProcName;

This replaces the previous post.

Important
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.

Locking spid’s

Use sp_who for a list of all spids. Then dbcc inputbuffer(spid) to find out what any blocking spid is executing. If needed you can then kill spid.