Over the course of the last couple years I got to know Microsoft’s SQL Server. I’m no DBA, but with a good night’s sleep and lots of Google searches, I’ve been able to get the job done. I’m also rather fond of collecting those things I find useful, so just in case my list would do you some good, here it is. If it’s in italics, you need to replace that with your stuff. I hope I got most of that right. Some might run on one version of SQL Server, but not another.
No promises, no warranties. Here goes.
Row Counts for Every Table in a Database
SELECT o.name, rows
FROM sysindexes i join sysobjects o on o.id=i.id
WHERE indid < 2 and type='U'
ORDER BY rows DESC
Get Information on All Columns for All Tables in a Database
SELECT table_schema, table_name, column_name, ordinal_position,
column_default, data_type, character_maximum_length
FROM information_schema.columns
The Last Time a Table was Updated For All Tables in a Database
SELECT DISTINCT OBJECT_NAME(object_id,database_id) as TableName, last_user_update
FROM database name.sys.dm_db_index_usage_stats
WHERE database_id = DB_ID('database name')
GROUP BY OBJECT_NAME(object_id,database_id), last_user_update
ORDER BY TableName
Find String in Any Object in a Database
SELECT DISTINCT so.name
FROM syscomments sc
INNER JOIN sysobjects so ON sc.id=so.id
WHERE sc.TEXT LIKE '%string%'
Backup a Database
BACKUP DATABASE databasename
TO DISK = 'path\name.bak';
Restore Database to Another Location
RESTORE DATABASE databasename
FROM DISK = 'path\name.bak'
WITH RECOVERY,
MOVE 'databasename_Data' TO 'newpath\databasename_Data.MDF'
MOVE 'databasename_Log' TO 'newpath\databasename_Data.LDF'
Copy Results from a Query to a Pipe-Delimited File
BCP "SELECT * FROM [database].[owner].[table_name]" queryout filename -Uuserid -Ppassword -t"|" -c -S server_name\instance
Change User Password
EXEC sp_password 'old password', 'new password', 'userid’
Select Rows Between Two Datetimes
SELECT *
FROM tablename
WHERE field BETWEEN '10/15/2015 00:00:00.00'
AND '10/15/2015 23:59:59.999'
Select Yesterday's Data
SELECT *
FROM table
WHERE date_field >= dateadd(day,datediff(day,1,GETDATE()),0)
AND date_field < dateadd(day,datediff(day,0,GETDATE()),0)
Count the Number of Occurances of Each Value
SELECT DISTINCT column_name, count(column_name) as CountOf
FROM tablename
GROUP BY column_name
Count Rows By Year
SELECT DISTINCT YEAR(datetime_field) as Year, COUNT(*) as Rows
FROM tablename
GROUP BY YEAR(datetime_field)
ORDER BY YEAR(datetime_field) DESC
Update Rows Based on a Time Difference in Minutes
UPDATE tablename
SET field = field
WHERE DATEDIFF(MINUTE,datetime,CURRENT_TIMESTAMP) < minutes
Replace String in a Field
UPDATE tablename
SET field = REPLACE(field, 'text', 'newtext')
SET field = REPLACE(field, 'text', 'newtext')
Replace Substring in a Column
UPDATE tablename
SET field = CAST(REPLACE(CAST(field as NVarchar(4000)),'string1','string2') AS NText)
WHERE field LIKE '%string1%'
Delete Rows from a Table Between Two Datetimes
DELETE FROM tablename
WHERE date_field BETWEEN 'mm/dd/yyyy 00:00:00.00' AND 'mm/dd/yyyy 23:59:59.999'
Left Join
SELECT A.field1, A.field2, B.field3
FROM tablename1 A
LEFT JOIN tablename2 B
ON A.field1=B.field1
