Get table size of the biggest tables in a database (SQL)

Following script will return a list of all the tables in your database with a size greater than 10MB.

SELECT 
    t.NAME AS TableName,
    s.Name AS SchemaName,
    p.rows AS RowCounts,
    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
	HAVING SUM(a.total_pages) * 8 / 1024.00 > 10
ORDER BY 
   TotalSpaceMB Desc

tableSize

Advertisements

Tips to improve SQL Server database design and performance

Best performance is the main concern to develop a successful application. Like a coin database is the tail side (back-end) of an application. A good database design provides best performance during data manipulation which results into the best performance of an application. Continue reading “Tips to improve SQL Server database design and performance”

Generate Create Statements for all indexes in a table

SELECT ' CREATE ' +
       CASE 
            WHEN I.is_unique = 1 THEN ' UNIQUE '
            ELSE ''
       END +
       I.type_desc COLLATE DATABASE_DEFAULT + ' INDEX ' +
       I.name + ' ON ' +
       SCHEMA_NAME(T.schema_id) + '.' + T.name + ' ( ' +
       KeyColumns + ' )  ' +
       ISNULL(' INCLUDE (' + IncludedColumns + ' ) ', '') +
       ISNULL(' WHERE  ' + I.filter_definition, '') + ' WITH ( ' +
       CASE 
            WHEN I.is_padded = 1 THEN ' PAD_INDEX = ON '
            ELSE ' PAD_INDEX = OFF '
       END + ',' +
       'FILLFACTOR = ' + CONVERT(
           CHAR(5),
           CASE 
                WHEN I.fill_factor = 0 THEN 100
                ELSE I.fill_factor
           END
       ) + ',' +
       -- default value 
       'SORT_IN_TEMPDB = OFF ' + ',' +
       CASE 
            WHEN I.ignore_dup_key = 1 THEN ' IGNORE_DUP_KEY = ON '
            ELSE ' IGNORE_DUP_KEY = OFF '
       END + ',' +
       CASE 
            WHEN ST.no_recompute = 0 THEN ' STATISTICS_NORECOMPUTE = OFF '
            ELSE ' STATISTICS_NORECOMPUTE = ON '
       END + ',' +
       ' ONLINE = OFF ' + ',' +
       CASE 
            WHEN I.allow_row_locks = 1 THEN ' ALLOW_ROW_LOCKS = ON '
            ELSE ' ALLOW_ROW_LOCKS = OFF '
       END + ',' +
       CASE 
            WHEN I.allow_page_locks = 1 THEN ' ALLOW_PAGE_LOCKS = ON '
            ELSE ' ALLOW_PAGE_LOCKS = OFF '
       END + ' ) ON [' +
       DS.name + ' ] ' +  CHAR(13) + CHAR(10) + ' GO' [CreateIndexScript]
FROM   sys.indexes I
       JOIN sys.tables T
            ON  T.object_id = I.object_id
       JOIN sys.sysindexes SI
            ON  I.object_id = SI.id
            AND I.index_id = SI.indid
       JOIN (
                SELECT *
                FROM   (
                           SELECT IC2.object_id,
                                  IC2.index_id,
                                  STUFF(
                                      (
                                          SELECT ' , ' + C.name + CASE 
                                                                       WHEN MAX(CONVERT(INT, IC1.is_descending_key)) 
                                                                            = 1 THEN 
                                                                            ' DESC '
                                                                       ELSE 
                                                                            ' ASC '
                                                                  END
                                          FROM   sys.index_columns IC1
                                                 JOIN sys.columns C
                                                      ON  C.object_id = IC1.object_id
                                                      AND C.column_id = IC1.column_id
                                                      AND IC1.is_included_column = 
                                                          0
                                          WHERE  IC1.object_id = IC2.object_id
                                                 AND IC1.index_id = IC2.index_id
                                          GROUP BY
                                                 IC1.object_id,
                                                 C.name,
                                                 index_id
                                          ORDER BY
                                                 MAX(IC1.key_ordinal) 
                                                 FOR XML PATH('')
                                      ),
                                      1,
                                      2,
                                      ''
                                  ) KeyColumns
                           FROM   sys.index_columns IC2 
                                  --WHERE IC2.Object_id = object_id('Person.Address') --Comment for all tables
                           GROUP BY
                                  IC2.object_id,
                                  IC2.index_id
                       ) tmp3
            )tmp4
            ON  I.object_id = tmp4.object_id
            AND I.Index_id = tmp4.index_id
       JOIN sys.stats ST
            ON  ST.object_id = I.object_id
            AND ST.stats_id = I.index_id
       JOIN sys.data_spaces DS
            ON  I.data_space_id = DS.data_space_id
       JOIN sys.filegroups FG
            ON  I.data_space_id = FG.data_space_id
       LEFT JOIN (
                SELECT *
                FROM   (
                           SELECT IC2.object_id,
                                  IC2.index_id,
                                  STUFF(
                                      (
                                          SELECT ' , ' + C.name
                                          FROM   sys.index_columns IC1
                                                 JOIN sys.columns C
                                                      ON  C.object_id = IC1.object_id
                                                      AND C.column_id = IC1.column_id
                                                      AND IC1.is_included_column = 
                                                          1
                                          WHERE  IC1.object_id = IC2.object_id
                                                 AND IC1.index_id = IC2.index_id
                                          GROUP BY
                                                 IC1.object_id,
                                                 C.name,
                                                 index_id 
                                                 FOR XML PATH('')
                                      ),
                                      1,
                                      2,
                                      ''
                                  ) IncludedColumns
                           FROM   sys.index_columns IC2 
                                  --WHERE IC2.Object_id = object_id('Person.Address') --Comment for all tables
                           GROUP BY
                                  IC2.object_id,
                                  IC2.index_id
                       ) tmp1
                WHERE  IncludedColumns IS NOT NULL
            ) tmp2
            ON  tmp2.object_id = I.object_id
            AND tmp2.index_id = I.index_id
WHERE  I.is_primary_key = 0
       AND I.is_unique_constraint = 0
           AND I.Object_id = object_id('tblERPInvoiceData') --Comment for all tables
           --AND I.name = 'IX_Address_PostalCode' --comment for all indexes

 
There is also one more on SQL Server Central:

https://www.sqlservercentral.com/Forums/Topic401784-562-3.aspx#bm1171799

Get every n-th record using SQL Server

First, a few things you should already know:
Modulus Returns the remainder of one number divided by another.
SELECT 32/10 -> 3
SELECT 32%10 -> 2

To return the n-th row only of a table, use:

WITH myTableWithRows AS (
    SELECT (ROW_NUMBER() OVER (ORDER BY myTable.SomeField)) as row,*
    FROM myTable)
SELECT * FROM myTableWithRows WHERE row = [value of n]

To return every n-th row in a table, use:

WITH myTableWithRows AS (
    SELECT (ROW_NUMBER() OVER (ORDER BY myTable.SomeField)) as row,*
    FROM myTable)
SELECT * FROM myTableWithRows WHERE [row]%[value of n] = 0

Example:

Return every 10th event from an events table:

WITH tableEvents AS (
    SELECT (ROW_NUMBER() OVER (ORDER BY EventDate)) as row,*
    FROM tblEvents
	Where Month(EventDate) = 7)
SELECT * FROM tableEvents WHERE [row]%10= 0

How to check if a constraint exists

In SQL Server, you can check if a constraint exists using the following script:

SELECT 
    OBJECT_NAME(OBJECT_ID) AS NameofConstraint
        ,SCHEMA_NAME(schema_id) AS SchemaName
        ,OBJECT_NAME(parent_object_id) AS TableName
        ,type_desc AS ConstraintType
    FROM sys.objects
    WHERE type_desc LIKE '%CONSTRAINT'
        AND OBJECT_NAME(OBJECT_ID)='[constraint name here]'

or by running one of the following commands:

SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS where TABLE_Name = '[TableName]'

SELECT *
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE CONSTRAINT_NAME='[constraint name here]'

--Returns one row for each FOREIGN KEY constrain
SELECT *
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
WHERE CONSTRAINT_NAME='[constraint name here]'

--Returns one row for each CHECK constraint
SELECT *
FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS
WHERE CONSTRAINT_NAME='[constraint name here]'

Enable and disable all indexes in a database

Disabling indexes is a good idea when it comes to loading large quantities of data, but… the big problem is clustered indexes. If you disable a clustered index, you’ve disabled the entire table.

Several options suggest themselves, and none of them are simple.

1) Loop through the system views (sys.indexes), extract the table and index name, generate and execute dynamic SQL to disable the index. Have an “undo” routine to re-enable them. (Be wary–was it a unique index or a unique constraint?) This, alas, only works if you do not use clustered indexes. Good luck with that.

2) As for 1, but skip any clustered indexes. When you load data, make sure it gets loaded in (clustered index) sequential order, otherwise you’ll have poor load times and fragmented tables. (If you data providers are like mine, good luck with that one, too.)

3) Create tables in your database containing definitions of the indexes on your “loading” tables. Build a routine that loops through them and drops all the indexes (clustered indexes last). This will be fast if you truncate the tables first. Load your data, then loop through and recreate the indexes from scratch (clustered first). Use table partitioning to make less horrible on the rest of the system (e.g. do all the above on the “loading” tables, then use partition switching to move the loaded data into your “live” tables). It took me no little time to build such a system, but it can and will work.

Disable script:

SELECT 'ALTER INDEX ' + QUOTENAME(I.name) + ' ON ' +  QUOTENAME(SCHEMA_NAME(T.schema_id))+'.'+ QUOTENAME(T.name) + ' DISABLE' 
FROM sys.indexes I
INNER JOIN sys.tables T ON I.object_id = T.object_id
WHERE I.type_desc = 'NONCLUSTERED'
AND I.name IS NOT NULL
AND I.is_disabled = 0

 

Enable script:

SELECT 'ALTER INDEX ' + QUOTENAME(I.name) + ' ON ' +  QUOTENAME(SCHEMA_NAME(T.schema_id))+'.'+ QUOTENAME(T.name) + ' REBUILD' 
FROM sys.indexes I
INNER JOIN sys.tables T ON I.object_id = T.object_id
WHERE I.type_desc = 'NONCLUSTERED'
AND I.name IS NOT NULL
AND I.is_disabled = 1