Drop Table if Exists

       IF OBJECT_ID(N'CensusReportData', N'U') IS NOT NULL

       DROP TABLE CensusReportData

Database and Logfile Sizes 

Database sizes


SELECT DB_NAME(database_id) AS DatabaseName,

Name AS Logical_Name, Physical_Name, (size*8)/1024 SizeMB FROM sys.master_files

--WHERE DB_NAME(database_id) = 'AdventureWorks'

GO


Logfile sizes

SELECT DB_NAME(database_id) AS DatabaseName,
Name AS Logical_Name,Physical_Name, (size*8)/1024 SizeMB
FROM sys.master_files
--WHERE DB_NAME(database_id) = 'AdventureWorks'
ORDER BY DB_NAME(database_id)

GO

Log file sizes v2

IF OBJECT_ID(N'master.dbo.TempForLogSpace', N'U') IS NOT NULL

  drop table master.dbo.TempForLogSpace

go

declare @sql_command nvarchar(MAX)

CREATE TABLE master.dbo.TempForLogSpace

(

DBName varchar(MAX),

LogSize real,

LogSpaceUsed real,

Status int

)

SELECT @sql_command = 'dbcc sqlperf (logspace)'

INSERT INTO master.dbo.TempForLogSpace EXEC (@sql_command)

--select * from master.dbo.TempForLogSpace

SELECT DB_NAME(database_id) AS DatabaseName,

sf.[Name] AS Logical_Name,

Physical_Name, (size*8)/1024 SizeMB,tmpLog.LogSpaceUsed ,state_desc,databasepropertyex(DB_NAME(database_id),'Recovery') as [Recovery],tmpLog.[Status]

FROM sys.master_files sf LEFT OUTER JOIN master.dbo.sysdatabases sd ON DB_NAME(database_id)=sf.[name]  LEFT OUTER JOIN master.dbo.TempForLogSpace tmpLog ON DB_NAME(database_id) = tmpLog.DBName

where physical_name like '%ldf%'

--WHERE DB_NAME(database_id) = 'nSightDB'

ORDER BY DB_NAME(database_id)

GO


sql table sizes


SELECT
    t.NAME AS TableName,
    s.Name AS SchemaName,
    p.rows,
    SUM(a.total_pages) * 8 AS TotalSpaceKB,
    CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB,
    SUM(a.used_pages) * 8 AS UsedSpaceKB,
    CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB,
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB,
    CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB
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 NOT LIKE 'dt%'
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255
GROUP BY
    t.Name, s.Name, p.Rows
ORDER BY
    TotalSpaceMB DESC, t.Name
   
    --https://stackoverflow.com/questions/7892334/get-size-of-all-tables-in-database

Index Sizes

SELECT
     [object_id]        = t.[object_id]
    ,[schema_name]      = s.[name]
    ,[table_name]       = t.[name]
    ,[index_name]       = CASE WHEN i.[type] in (0,1,5) THEN null    ELSE i.[name] END -- 0=Heap; 1=Clustered; 5=Clustered Columnstore
    ,[object_type]      = CASE WHEN i.[type] in (0,1,5) THEN 'TABLE' ELSE 'INDEX'  END
    ,[index_type]       = i.[type_desc]
    ,[partition_count]  = p.partition_count
    ,[row_count]        = p.[rows]
    ,[data_compression] = CASE WHEN p.data_compression_cnt > 1 THEN 'Mixed'
                               ELSE (  SELECT DISTINCT p.data_compression_desc
                                       FROM sys.partitions p
                                       WHERE i.[object_id] = p.[object_id] AND i.index_id = p.index_id
                                    )
                          END
    ,[total_space_MB]   = cast(round(( au.total_pages                  * (8/1024.00)), 2) AS DECIMAL(36,2))
    ,[used_space_MB]    = cast(round(( au.used_pages                   * (8/1024.00)), 2) AS DECIMAL(36,2))
    ,[unused_space_MB]  = cast(round(((au.total_pages - au.used_pages) * (8/1024.00)), 2) AS DECIMAL(36,2))
FROM sys.schemas s
JOIN sys.tables  t ON s.schema_id = t.schema_id
JOIN sys.indexes i ON t.object_id = i.object_id
JOIN (
    SELECT [object_id], index_id, partition_count=count(*), [rows]=sum([rows]), data_compression_cnt=count(distinct [data_compression])
    FROM sys.partitions
    GROUP BY [object_id], [index_id]
) p ON i.[object_id] = p.[object_id] AND i.[index_id] = p.[index_id]
JOIN (
    SELECT p.[object_id], p.[index_id], total_pages = sum(a.total_pages), used_pages = sum(a.used_pages), data_pages=sum(a.data_pages)
    FROM sys.partitions p
    JOIN sys.allocation_units a ON p.[partition_id] = a.[container_id]
    GROUP BY p.[object_id], p.[index_id]
) au ON i.[object_id] = au.[object_id] AND i.[index_id] = au.[index_id]
WHERE t.is_ms_shipped = 0 -- Not a system table

Rebuild all indexes on DB's or Rebuilt all indexes on a specific DB's

--https://www.mssqltips.com/sqlservertip/1367/sql-server-script-to-rebuild-all-indexes-for-all-tables-and-all-databases/
--Rebuild all indexes on DB's or Rebuilt all indexes on specific DB's
DECLARE @Database NVARCHAR(255)  
DECLARE @Table NVARCHAR(255)  
DECLARE @cmd NVARCHAR(1000)  

DECLARE DatabaseCursor CURSOR READ_ONLY FOR  
SELECT name FROM master.sys.databases  
----------------------------------------------------------------------
----------------------------------------------------------------------
--WHERE name NOT IN ('master','msdb','tempdb','model','distribution')  -- databases to exclude
WHERE name IN ('TMS_Live') -- use this to select specific databases and comment out line above
----------------------------------------------------------------------
----------------------------------------------------------------------

AND state = 0 -- database is online
AND is_in_standby = 0 -- database is not read only for log shipping
ORDER BY 1  

OPEN DatabaseCursor  

FETCH NEXT FROM DatabaseCursor INTO @Database  
WHILE @@FETCH_STATUS = 0  
BEGIN  

   SET @cmd = 'DECLARE TableCursor CURSOR READ_ONLY FOR SELECT ''['' + table_catalog + ''].['' + table_schema + ''].['' +  
   table_name + '']'' as tableName FROM [' + @Database + '].INFORMATION_SCHEMA.TABLES WHERE table_type = ''BASE TABLE'''  

   -- create table cursor  
   EXEC (@cmd)  
   OPEN TableCursor  

   FETCH NEXT FROM TableCursor INTO @Table  
   WHILE @@FETCH_STATUS = 0  
   BEGIN
      BEGIN TRY  
         SET @cmd = 'ALTER INDEX ALL ON ' + @Table + ' REBUILD'
         PRINT @cmd -- uncomment if you want to see commands
         EXEC (@cmd)
      END TRY
      BEGIN CATCH
         PRINT '---'
         PRINT @cmd
         PRINT ERROR_MESSAGE()
         PRINT '---'
      END CATCH

      FETCH NEXT FROM TableCursor INTO @Table  
   END  

   CLOSE TableCursor  
   DEALLOCATE TableCursor  

   FETCH NEXT FROM DatabaseCursor INTO @Database  
END  
CLOSE DatabaseCursor  
DEALLOCATE DatabaseCursor