At some point in the past I've used each of the following SQL statements to accomplish some required task and not wanting to Google Search for the same things twice, staring collecting them in a list for faster reference. Some of these may not work with all versions of SQL Server and as new features are introduced may not be the best way to perform a specific task.
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')
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
