My Collection of SQL

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')

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