SQL Statements

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