T-SQL Example code: useful stores procedures and snippets

Here are some useful one liners that I find very useful

Scriplets

EXEC sp_MSforeachdb 'USE ?; PRINT DB_NAME()'
EXEC sp_MSforeachdb 'USE ? EXEC sp_helpfile;'
EXEC sp_MSforeachdb 'USE ? SELECT DB_NAME() + ''.'' + OBJECT_NAME(object_Id) FROM sys.tables'
EXEC sp_MSforeachdb 'USE ? SELECT OBJECT_NAME(object_Id) 

FROM sys.tables where DB_NAME() NOT IN

(''master'', ''model'', ''msdb'', ''tempdb'')'
exec sp_MSforeachdb 'use [?];

print ''?'';SELECT db_name(); 

select name from ?..sysusers where uid <16000'

Convert varchar date to date and sort

select * FROM DatabaseName order by convert(datetime,LastCheck,103) desc

Remove Space from Fields

update dns

set columnName = rtrim(ltrim(columnName))

or

UPDATE dns

SET columnName = REPLACE(columnName, ' ', '')

Kill all connections to a database

alter database DatabaseName set single_user with rollback immediate
alter database DatabaseName set multi_user with rollback immediate

Useful script to sort a suspect SQL DB

EXEC sp_resetstatus 'DatabaseName'; 

ALTER DATABASE DatabaseName SET EMERGENCY 

DBCC checkdb('DatabaseName') 

ALTER DATABASE DatabaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE 

DBCC CheckDB ('DatabaseName', REPAIR_ALLOW_DATA_LOSS) 

ALTER DATABASE DatabaseName SET MULTI_USER

Break Mirror and fix Recovering state

USE DatabaseName

GO

ALTER DATABASE DatabaseName SET PARTNER OFF
RESTORE DATABASE DatabaseName WITH NORECOVERY
RESTORE DATABASE DatabaseName WITH RECOVERY

Then take offline / delete etc.

To take a database out of Restoring state

RESTORE DATABASE DatabaseName WITH RECOVERY

List all databases that are not a system database (2005+)

SELECT name

FROM master..sysdatabases

where name not in ('master', 'tempdb', 'msdb', 'pubs', 

'model', 'reportserver', 'reportservertempdb')

Useful Stored Procedures

SP Name Function 
sp_helpfile
 returns the full file path of all files associated with database 
sp_detach_db 'mydb'
 Detaches a database called mydb 
sp_attach_db 'mydb','E:\mydb.mdf','E:\mydblog.ldf'
 Attaches the specified files as mydb 
sp_MSforeachdb 'use [?];SELECT db_name(); select * from….'
 run’s the specified tsql against all databases

misc code snippets

select name from sysobjects where type = 'U' and name like '%_temp' order by name

The folllowing script generates another script which will search all columns in all tables for specific text.

SET NOCOUNT ON;
SELECT 'SELECT * FROM [' + TABLE_SCHEMA + '].[' + TABLE_NAME
+ '] WHERE ['+COLUMN_NAME+'] LIKE ''%SEARCH-TEXT%'''
FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE LIKE '%CHAR'
ORDER BY 1
Posted on July 12, 2012 at 23:41 by simon · Permalink
In: SQL · Tagged with: , , , , , , , , ,

Leave a Reply