lockworld
6/19/2017 - 7:52 PM

SQL Server Database and Server commands (These are for when you need to go beyond just table-level access).

SQL Server Database and Server commands (These are for when you need to go beyond just table-level access).

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE' 
ORDER BY TABLE_NAME
--https://stackoverflow.com/a/1171031

SELECT
        [name]
       ,create_date
       ,modify_date
FROM
        sys.tables
ORDER BY create_date DESC, modify_date DESC
select * from INFORMATION_SCHEMA.COLUMNS 
order by TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME
This set of gists helps when working with tables in SQL Server
/*
Use this query to find the start time, percent complete, and estimated completion time for a running backup or restore process
*/

SELECT session_id AS SPID
	,command
	,percent_complete
	,start_time
	,dateadd(second, estimated_completion_time / 1000, getdate()) AS estimated_completion_time
	,a.TEXT AS Query
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) a
WHERE r.command IN (
		'BACKUP DATABASE'
		,'RESTORE DATABASE'
		)
SELECT 
    t.NAME AS TableName,    
    p.rows AS RowCounts,
    CONVERT(DECIMAL,SUM(a.total_pages)) * 8 / 1024 / 1024 AS TotalSpaceGB, 
    SUM(a.used_pages)  * 8 / 1024 / 1024 AS UsedSpaceGB , 
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 / 1024 / 1024 AS UnusedSpaceGB
FROM 
    sys.tables t
INNER JOIN      
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN 
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN 
    sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN 
    sys.schemas s ON t.schema_id = s.schema_id
WHERE 
    t.NAME IN ('TABLE01','TABLE02')
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255 
GROUP BY 
    t.Name, s.Name, p.Rows
ORDER BY 
    UsedSpaceGB DESC, t.Name