Drop If Exists - Normal Table

IF EXISTS (SELECT 1 FROM DATABASE_NAME.information_schema.tables 
WHERE table_name = 'TABLE_NAME'
AND table_schema = 'dbo')
DROP TABLE DATABASE_NAME.SCHEMA.TABLE_NAME

Drop If Exists - Temp Table

IF OBJECT_ID('tempdb..#TABLE_NAME') IS NOT NULL
DROP TABLE #TABLE_NAME

Change a Nullable column to NOT NULL with Default Value

ALTER TABLE dbo.MyTable
ADD CONSTRAINT my_Con DEFAULT GETDATE() for created

ALTER TABLE dbo.MyTable 
ALTER COLUMN Created DATETIME NOT NULL

Get list of all tables and row counts

Also good to check progress of a insert - shows count before an insert is complete

SELECT c.name +'.'+ a.name TableName, SUM (b.rows) RowCount
FROM sys.tables a INNER JOIN 
sys.partitions b
ON b.OBJECT_ID = a.OBJECT_ID INNER JOIN 
sys.schemas c 
ON a.schema_id = c.schema_id
WHERE a.is_ms_shipped = 0 
AND b.index_id IN (1, 0)
-- AND a.name in ('')
GROUP BY c.name, a.name
ORDER BY SUM (b.rows) DESC

All Databases Data & log file size, space used & free space

http://gallery.technet.microsoft.com/scriptcenter/All-Databases-Data-log-a36da95d

------------------------------Data file size---------------------------- 
if exists (select * from tempdb.sys.all_objects where name like '%#dbsize%') 
drop table #dbsize 
create table #dbsize 
(Dbname sysname,dbstatus varchar(50),Recovery_Model varchar(40) default ('NA'), file_Size_MB decimal(30,2)default (0),Space_Used_MB decimal(30,2)default (0),Free_Space_MB decimal(30,2) default (0)) 
go 
  
insert into #dbsize(Dbname,dbstatus,Recovery_Model,file_Size_MB,Space_Used_MB,Free_Space_MB) 
exec sp_msforeachdb 
'use [?]; 
  select DB_NAME() AS DbName, 
    CONVERT(varchar(20),DatabasePropertyEx(''?'',''Status'')) ,  
    CONVERT(varchar(20),DatabasePropertyEx(''?'',''Recovery'')),  
sum(size)/128.0 AS File_Size_MB, 
sum(CAST(FILEPROPERTY(name, ''SpaceUsed'') AS INT))/128.0 as Space_Used_MB, 
SUM( size)/128.0 - sum(CAST(FILEPROPERTY(name,''SpaceUsed'') AS INT))/128.0 AS Free_Space_MB  
from sys.database_files  where type=0 group by type' 
  
  
  
  
  
go 
  
-------------------log size-------------------------------------- 
  if exists (select * from tempdb.sys.all_objects where name like '#logsize%') 
drop table #logsize 
create table #logsize 
(Dbname sysname, Log_File_Size_MB decimal(38,2)default (0),log_Space_Used_MB decimal(30,2)default (0),log_Free_Space_MB decimal(30,2)default (0)) 
go 
  
insert into #logsize(Dbname,Log_File_Size_MB,log_Space_Used_MB,log_Free_Space_MB) 
exec sp_msforeachdb 
'use [?]; 
  select DB_NAME() AS DbName, 
sum(size)/128.0 AS Log_File_Size_MB, 
sum(CAST(FILEPROPERTY(name, ''SpaceUsed'') AS INT))/128.0 as log_Space_Used_MB, 
SUM( size)/128.0 - sum(CAST(FILEPROPERTY(name,''SpaceUsed'') AS INT))/128.0 AS log_Free_Space_MB  
from sys.database_files  where type=1 group by type' 
  
  
go 
--------------------------------database free size 
  if exists (select * from tempdb.sys.all_objects where name like '%#dbfreesize%') 
drop table #dbfreesize 
create table #dbfreesize 
(name sysname, 
database_size varchar(50), 
Freespace varchar(50)default (0.00)) 
  
insert into #dbfreesize(name,database_size,Freespace) 
exec sp_msforeachdb 
'use [?];SELECT database_name = db_name() 
    ,database_size = ltrim(str((convert(DECIMAL(15, 2), dbsize) + convert(DECIMAL(15, 2), logsize)) * 8192 / 1048576, 15, 2) + ''MB'') 
    ,''unallocated space'' = ltrim(str(( 
                CASE  
                    WHEN dbsize >= reservedpages 
                        THEN (convert(DECIMAL(15, 2), dbsize) - convert(DECIMAL(15, 2), reservedpages)) * 8192 / 1048576 
                    ELSE 0 
                    END 
                ), 15, 2) + '' MB'') 
FROM ( 
    SELECT dbsize = sum(convert(BIGINT, CASE  
                    WHEN type = 0 
                        THEN size 
                    ELSE 0 
                    END)) 
        ,logsize = sum(convert(BIGINT, CASE  
                    WHEN type <> 0 
                        THEN size 
                    ELSE 0 
                    END)) 
    FROM sys.database_files 
) AS files 
,( 
    SELECT reservedpages = sum(a.total_pages) 
        ,usedpages = sum(a.used_pages) 
        ,pages = sum(CASE  
                WHEN it.internal_type IN ( 
                        202 
                        ,204 
                        ,211 
                        ,212 
                        ,213 
                        ,214 
                        ,215 
                        ,216 
                        ) 
                    THEN 0 
                WHEN a.type <> 1 
                    THEN a.used_pages 
                WHEN p.index_id < 2 
                    THEN a.data_pages 
                ELSE 0 
                END) 
    FROM sys.partitions p 
    INNER JOIN sys.allocation_units a 
        ON p.partition_id = a.container_id 
    LEFT JOIN sys.internal_tables it 
        ON p.object_id = it.object_id 
) AS partitions' 
----------------------------------- 
  
  
  
if exists (select * from tempdb.sys.all_objects where name like '%#alldbstate%') 
drop table #alldbstate  
create table #alldbstate  
(dbname sysname, 
DBstatus varchar(55), 
R_model Varchar(30)) 
   
--select * from sys.master_files 
  
insert into #alldbstate (dbname,DBstatus,R_model) 
select name,CONVERT(varchar(20),DATABASEPROPERTYEX(name,'status')),recovery_model_desc from sys.databases 
--select * from #dbsize 
  
insert into #dbsize(Dbname,dbstatus,Recovery_Model) 
select dbname,dbstatus,R_model from #alldbstate where DBstatus <> 'online' 
  
insert into #logsize(Dbname) 
select dbname from #alldbstate where DBstatus <> 'online' 
  
insert into #dbfreesize(name) 
select dbname from #alldbstate where DBstatus <> 'online' 
  
select  
  
d.Dbname,d.dbstatus,d.Recovery_Model, 
(file_size_mb + log_file_size_mb) as DBsize, 
d.file_Size_MB,d.Space_Used_MB,d.Free_Space_MB, 
l.Log_File_Size_MB,log_Space_Used_MB,l.log_Free_Space_MB,fs.Freespace as DB_Freespace 
from #dbsize d join #logsize l  
on d.Dbname=l.Dbname join #dbfreesize fs  
on d.Dbname=fs.name 
order by Dbname

Move database files

Get the logical name of the data and log files associated with the database by typing the following

SELECT b.name as DatabaseName, a.name as LogicalName, a.physical_name as PhysicalName
FROM sys.master_files a INNER JOIN 
sys.databases b
ON a.database_id = b.database_id
ORDER BY b.name, a.name

Tell SQL Server where you will move the file
- This does not actually move anything, and SQL Server does not look at this new location until next time the database is "restarted"
- ```
ALTER DATABASE Personnel MODIFY FILE ( NAME = Personnel_Data, FILENAME = "C:\Data\Personnel_Data.mdf")
```
Take database offline
- This leaves the database in the tree
- Releases SQL Server lock on the physical files
- ```
ALTER DATABASE Personnel SET offline
```
Move the physical files
Bring database back online
- ```
ALTER DATABASE Personnel SET online
```

List Databases Part of Availability Group

SELECT dbcs.database_name AS [DatabaseName]
FROM master.sys.availability_groups AS AG LEFT OUTER JOIN 
master.sys.dm_hadr_availability_group_states as agstates
ON AG.group_id = agstates.group_id INNER JOIN 
master.sys.availability_replicas AS AR
ON AG.group_id = AR.group_id INNER JOIN 
master.sys.dm_hadr_availability_replica_states AS arstates
ON AR.replica_id = arstates.replica_id 
AND arstates.is_local = 1 INNER JOIN 
master.sys.dm_hadr_database_replica_cluster_states AS dbcs
ON arstates.replica_id = dbcs.replica_id LEFT OUTER JOIN 
master.sys.dm_hadr_database_replica_states AS dbrs
ON dbcs.replica_id = dbrs.replica_id 
AND dbcs.group_database_id = dbrs.group_database_id
WHERE ISNULL (arstates.role, 3) = 2 
AND ISNULL (dbcs.is_database_joined, 0) = 1
AND d.name = dbcs.database_name

Exit from Stored Procedure

```
RETURN [ integer_expression ]
```
Exits unconditionally from a query or procedure. RETURN is immediate and complete and can be used at any point to exit from a procedure, batch, or statement block. Statements that follow RETURN are not executed.

List Tables, columns, datatypes, constraints, related columns

SELECT aa.TABLE_CATALOG as DATABASE_NAME
, aa.TABLE_SCHEMA
, aa.TABLE_NAME
, aa.COLUMN_NAME
, aa.ORDINAL_POSITION
, ISNULL (REPLACE (REPLACE (aa.COLUMN_DEFAULT, CHAR(10), ''), CHAR(13), ''), '') as COLUMN_DEFAULT
, aa.IS_NULLABLE
, aa.DATA_TYPE
, ISNULL (CAST (CHARACTER_MAXIMUM_LENGTH as varchar), '') as CHARACTER_LENGTH
, ISNULL (a.CONSTRAINT_NAME, '') as CONSTRAINT_NAME
, ISNULL (b.CONSTRAINT_TYPE, '') AS CONSTRAINT_TYPE
, (SELECT ISNULL (CAST (MAX (x.ORDINAL_POSITION) as varchar), '') 
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE x
WHERE c.CONSTRAINT_CATALOG = x.CONSTRAINT_CATALOG
AND c.CONSTRAINT_SCHEMA = x.CONSTRAINT_SCHEMA
AND c.CONSTRAINT_NAME = x.CONSTRAINT_NAME
) as NUMBER_COLUMNS_IN_CONSTRAINT 
, ISNULL (c.TABLE_SCHEMA, '') as REFERENCED_TABLE_SCHEMA
, ISNULL (c.TABLE_NAME, '') as REFERENCED_TABLE_NAME
, ISNULL (c.COLUMN_NAME, '') as REFERENCED_COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS aa LEFT OUTER JOIN
INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE a 
ON aa.TABLE_CATALOG = a.TABLE_CATALOG
AND aa.TABLE_SCHEMA = a.TABLE_SCHEMA
AND aa.TABLE_NAME = a.TABLE_NAME
AND aa.COLUMN_NAME = a.COLUMN_NAME LEFT OUTER JOIN
INFORMATION_SCHEMA.TABLE_CONSTRAINTS b 
ON a.TABLE_CATALOG = b.CONSTRAINT_CATALOG
AND a.TABLE_SCHEMA = b.CONSTRAINT_SCHEMA
AND a.TABLE_NAME = b.TABLE_NAME
AND a.CONSTRAINT_NAME = b.CONSTRAINT_NAME LEFT OUTER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
ON a.TABLE_CATALOG = c.CONSTRAINT_CATALOG
AND a.TABLE_SCHEMA = c.CONSTRAINT_SCHEMA
AND a.CONSTRAINT_NAME = c.CONSTRAINT_NAME
ORDER BY aa.TABLE_CATALOG, aa.TABLE_SCHEMA, aa.TABLE_NAME, 
aa.ORDINAL_POSITION, a.CONSTRAINT_NAME, a.COLUMN_NAME, c.TABLE_NAME, c.COLUMN_NAME

List table names; create, access, update date; number columns and rows; space in total, table, and indexes

SELECT b.name + '.' + a.name as tableName
, a.create_date as createDate
, c.lastAccessDate
, c.lastUpdateDate
, a.max_column_id_used as numberOfColumns
, d.numberOfRows
, e.totalSpaceReservedKB
, e.totalSpaceUsedKB
, e.totalSpaceUnusedKB
, e.numberOfIndexes
, e.tableSpaceReservedKB
, e.tableSpaceUsedKB
, e.tableSpaceUnusedKB
, e.indexSpaceReservedKB
, e.indexSpaceUsedKB
, e.indexSpaceUnusedKB
FROM sys.tables a INNER JOIN
sys.schemas b
ON a.schema_id = b.schema_id LEFT OUTER JOIN
(SELECT x.object_id
, OBJECT_NAME(x.object_id) as tableName
, (SELECT MAX (inlineTable.d) 
FROM (VALUES (MAX (x.last_user_seek))
, (MAX (x.last_user_scan))
, (MAX (x.last_user_lookup))
) as inlineTable (d)
) as lastAccessDate
, MAX (last_user_update) as lastUpdateDate
FROM sys.dm_db_index_usage_stats AS x
WHERE x.database_id = DB_ID()
AND x.object_id = OBJECT_ID('testLastAccess')
GROUP BY x.object_id, OBJECT_NAME(x.object_id)
) c
ON a.object_id = c.object_id LEFT OUTER JOIN
(SELECT 
x.object_id
, z.name + '.' + x.name as tableName
, SUM (y.rows) as numberOfRows
FROM sys.tables x INNER JOIN 
sys.partitions y
ON x.OBJECT_ID = y.OBJECT_ID INNER JOIN 
sys.schemas z
ON x.schema_id = z.schema_id
WHERE x.is_ms_shipped = 0 
AND y.index_id IN (1, 0)
GROUP BY x.object_id, z.name, x.name
) d
ON a.object_id = d.object_id LEFT OUTER JOIN
(SELECT 
r.name + '.' + m.NAME AS tableName
, m.object_id
, SUM (CASE WHEN n.type_desc IN ('NONCLUSTERED') 
THEN 1 
ELSE 0 END) as numberOfIndexes
, SUM (q.total_pages) * 8 AS totalSpaceReservedKB
, SUM (q.used_pages) * 8 AS totalSpaceUsedKB
, (SUM (q.total_pages) - SUM(q.used_pages)) * 8 AS totalSpaceUnusedKB

, SUM (CASE WHEN n.type_desc IN ('CLUSTERED', 'HEAP') 
THEN q.total_pages 
ELSE 0 END) * 8 AS tableSpaceReservedKB 
, SUM (CASE WHEN n.type_desc IN ('CLUSTERED', 'HEAP') 
THEN q.used_pages 
ELSE 0 END) * 8 AS tableSpaceUsedKB
, (SUM (CASE WHEN n.type_desc IN ('CLUSTERED', 'HEAP') 
THEN q.total_pages 
ELSE 0 END) 
- SUM (CASE WHEN n.type_desc IN ('CLUSTERED', 'HEAP') 
THEN q.used_pages 
ELSE 0 END)) * 8 AS tableSpaceUnusedKB
, SUM (CASE WHEN n.type_desc IN ('NONCLUSTERED') 
THEN q.total_pages 
ELSE 0 END) * 8 AS indexSpaceReservedKB 
, SUM (CASE WHEN n.type_desc IN ('NONCLUSTERED') 
THEN q.used_pages 
ELSE 0 END) * 8 AS indexSpaceUsedKB
, (SUM (CASE WHEN n.type_desc IN ('NONCLUSTERED') 
THEN q.total_pages 
ELSE 0 END) 
- SUM (CASE WHEN n.type_desc IN ('NONCLUSTERED') 
THEN q.used_pages 
ELSE 0 END)) * 8 AS indexSpaceUnusedKB
FROM sys.tables m INNER JOIN      
sys.indexes n 
ON m.OBJECT_ID = n.object_id INNER JOIN 
sys.partitions p 
ON n.object_id = p.OBJECT_ID 
AND n.index_id = p.index_id INNER JOIN 
sys.allocation_units q 
ON p.partition_id = q.container_id LEFT OUTER JOIN 
sys.schemas r 
ON m.schema_id = r.schema_id
WHERE m.NAME NOT LIKE 'dt%' 
AND m.is_ms_shipped = 0
AND n.OBJECT_ID > 255 
GROUP BY m.object_id, m.Name, r.Name, p.Rows
) e
ON a.object_id = e.object_id
WHERE a.NAME NOT LIKE 'dt%' 
AND a.is_ms_shipped = 0
AND a.OBJECT_ID > 255 
ORDER BY b.name + '.' + a.name

List size used by indexes

SELECT 
r.name + '.' + m.NAME AS tableName
, m.object_id
, n.type_desc as indexType
, n.index_id
, n.name as indexName

, SUM (q.total_pages) * 8 AS indexSpaceReservedKB 
, SUM (q.used_pages) * 8 AS indexSpaceUsedKB
, (SUM (q.total_pages) - SUM (q.used_pages)) * 8 AS indexSpaceUnusedKB
FROM sys.tables m INNER JOIN      
sys.indexes n 
ON m.OBJECT_ID = n.object_id INNER JOIN 
sys.partitions p 
ON n.object_id = p.OBJECT_ID 
AND n.index_id = p.index_id INNER JOIN 
sys.allocation_units q 
ON p.partition_id = q.container_id LEFT OUTER JOIN 
sys.schemas r 
ON m.schema_id = r.schema_id
WHERE m.NAME NOT LIKE 'dt%' 
AND m.is_ms_shipped = 0
AND n.OBJECT_ID > 255 
GROUP BY m.object_id, m.Name, n.index_id, n.type_desc, n.name, r.Name, p.Rows
ORDER BY r.name, m.name, n.type_desc, n.name

Error log

Display error log
- ```
EXEC master..Xp_readerrorlog
```

Filter error log

EXEC master..Xp_readerrorlog 0, 1, N'FINDME'

Get SQL Server Agent Startup Time

Can't confirm this is 100.0% perfect, but seems to work at least 99.9% of time from online searches
- ```
SELECT login_time
FROM sys.sysprocesses
WHERE program_name LIKE 'SQLAgent - Generic Refresher%'
```
If SQL Server Agent log hasn't been cycled, otherwise, change the "0" to a higher number, up to 9 (more after 2008, I think)
- ```
EXEC sp_readerrorlog 0, 2, 'SQLServerAgent starting'
```

MS SQL Server Snippets

Drop If Exists - Normal Table

Drop If Exists - Temp Table

Change a Nullable column to NOT NULL with Default Value

Get list of all tables and row counts

All Databases Data & log file size, space used & free space

Move database files

List Databases Part of Availability Group

Exit from Stored Procedure

List Tables, columns, datatypes, constraints, related columns

List table names; create, access, update date; number columns and rows; space in total, table, and indexes

List size used by indexes

Error log

Get SQL Server Agent Startup Time