See this link for details on how to use
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
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,
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"
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.
Create a temporary table, e.g
CREATE TABLE #GlobalVariables (variableOne varchar(30), variableTwo int)
INSERT INTO #GlobalVariables (variableOne, variableTwo) VALUES ('ABCdef', 222222)
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)
A link to a blog that can be used as the starting point for those who need to manipulate XML held in MS-SQL.
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'
To get the keys for a table in Microsoft SQL Server use the stored procedure: sp_pkeys
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.
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.
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.
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