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.

Clearing out SQL server engines in the connection list

The engines are held in a file on the client PC not in the registry.

Link to an article here.

Clear SQL Server’s cache

Without restarting the service or rebooting the host machine. dbcc freesystemcache dbcc freesessioncache dbcc freeproccache