T-SQL

Get size of all tables in a database

SELECT
        T.NAME AS TableName,
        S.Name AS SchemaName,
        P.rows AS RowCounts,
        SUM(AU.total_pages) * 8 / 1024 AS TotalSpaceMB,
        SUM(AU.used_pages) * 8 / 1024 AS UsedSpaceMB
FROM
        syS.tables T
        JOIN sys.indexes I ON T.object_id = I.object_id
        JOIN sys.partitions P ON I.object_id = P.object_id AND I.index_id = P.index_id
        JOIN sys.allocation_units AU ON P.partition_id = AU.container_id
        JOIN sys.schemas S ON T.schema_id = S.schema_id
GROUP BY
        T.Name, S.Name, P.Rows
ORDER BY
        TotalSpaceMB DESC

Find out the disk size of an index

SELECT
        OBJECT_NAME(I.OBJECT_ID) AS table_name,
        I.name AS index_name,  
        I.type_desc,
        AU.used_pages,
    8 * SUM(AU.used_pages) AS 'index_size_KB',         
    CAST(8 * SUM(AU.used_pages) / 1024.0 AS DECIMAL(18,2)) AS 'Index size (MB)'
FROM
    sys.indexes I
        JOIN sys.partitions P ON P.OBJECT_ID = I.OBJECT_ID AND P.index_id = I.index_id
        JOIN sys.allocation_units AU ON AU.container_id = P.partition_id
--WHERE
--      OBJECT_NAME(I.OBJECT_ID) = '<tableName>'        
GROUP BY
    I.object_id, I.name, I.type_desc, AU.used_pages
ORDER BY
    table_name;

How to delete job history (sp_purge_jobhistory)

Remove History for all jobs:

USE msdb ;
EXEC dbo.sp_purge_jobhistory;

By specifying the , we can remove the history of that job:

USE msdb ;
EXEC dbo.sp_purge_jobhistory @job_name = N'<job name>';
GO

To delete history for a specific job up to specific date:

USE msdb ;
EXEC dbo.sp_purge_jobhistory @job_name = N'<job name>', @oldest_date = '<date>';

Renaming the physical file names for Datafiles in SQL Server

  • Update data file location
    ALTER DATABASE <your-database-name>
    MODIFY FILE (NAME = logical_file_name, FILENAME = '<full-path-of-your-mdf-file' )
  • Update log file location (optionally)
    ALTER DATABASE <your-database-name>
    MODIFY FILE (NAME = logical_file_name, FILENAME = '<full-path-of-your-ldf-file' )
  • Detach the database
  • Rename the file(s) on disk
  • Re-attach the database

Drop a list of stored procedures whose names begin with a certain string

DECLARE @SPName AS NVARCHAR(128)
       
-- For each SP...
DECLARE MyCursor CURSOR
FOR
SELECT OBJECT_NAME(M.object_id) as SPName
FROM
        sys.sql_modules M
        INNER JOIN sys.objects O ON M.object_id = O.object_id
WHERE
        O.type_desc = 'SQL_STORED_PROCEDURE'
        AND OBJECT_NAME(object_id) like '<Prefix>%'

OPEN MyCursor
FETCH NEXT FROM MyCursor INTO @SPName

WHILE @@FETCH_STATUS = 0
BEGIN
        PRINT 'Deleting SP ' + @SPName
        EXEC ('DROP PROCEDURE ' + @SPName)  
               
        FETCH NEXT FROM MyCursor INTO @SPName
END
CLOSE MyCursor
DEALLOCATE MyCursor

How to drop a list of tables, including the associated foreign key constraints

DECLARE
     @TableName AS SYSNAME
   , @RefTableName AS SYSNAME
   , @ConstraintName AS SYSNAME

-- For each table...
WHILE 1 = 1
BEGIN
   SET @TableName = NULL
   
   SELECT TOP 1 @TableName = name FROM sys.tables WHERE name LIKE '<TablePrefix>%'
   IF @TableName IS NULL
      BREAK;
       
      -- For each FK referring this table
      WHILE 1 = 1
      BEGIN
         SET @ConstraintName = NULL
                 
         SELECT
            @ConstraintName = FK.name,
            @RefTableName = OBJECT_NAME(C.parent_object_id)
         FROM
            sys.foreign_keys AS FK
            INNER JOIN sys.foreign_key_columns AS C
                        ON FK.OBJECT_ID = C.constraint_object_id
            INNER JOIN sys.tables T ON t.OBJECT_ID = C.referenced_object_id
         WHERE
            OBJECT_NAME (FK.referenced_object_id) = @TableName

         IF @ConstraintName IS NULL
            BREAK;

         PRINT 'Dropping constraint ' + @ConstraintName
         EXEC ('ALTER TABLE [' + @RefTableName + '] DROP CONSTRAINT ' + @ConstraintName)
      END        
       
      PRINT 'Dropping table ' + @TableName
      EXEC ('DROP TABLE [' + @TableName + ']') 
END

GO

List all stored procedures that are using CLR functions/procedures

The following takes each CLR function/procedures and returns all stored procedures/function that are using it:

SELECT DISTINCT
        O.name AS ClrObjectName,
        OBJECT_NAME(SM.object_id) AS IsUsedInObject,
        AM.assembly_class + '.' + AM.assembly_method AS AssemblyMethod
FROM   
        sys.assembly_modules AM
        INNER JOIN sys.assemblies A ON A.assembly_id = AM.assembly_id
        INNER JOIN sys.objects O ON O.object_id = AM.object_id
        INNER JOIN sys.sql_modules SM ON SM.definition LIKE '%' + O.name + '%'
ORDER BY 1, 2

List of all CLR functions and procedures associated with a .NET assembly

The following script returns one row for each function, procedure or trigger that is defined by a common language runtime (CLR) assembly:

SELECT
        SCHEMA_NAME(O.schema_id) AS [Schema], O.name,
        A.name AS assembly_name, AM.assembly_class,
        AM.assembly_method,
        A.permission_set_desc,
        O.[type_desc]
FROM
        sys.assembly_modules AM
        INNER JOIN sys.assemblies A ON A.assembly_id = AM.assembly_id
        INNER JOIN sys.objects O ON O.object_id = AM.object_id
ORDER BY
        A.name, AM.assembly_class

More details on Books online.

Get all foreign keys that refer a specific table and column

SELECT
        OBJECT_NAME(constraint_object_id) as FKName,
        OBJECT_NAME(parent_object_id) as TableName,
        COL_NAME(parent_object_id, parent_column_id) AS ColumnName,
        OBJECT_NAME(referenced_object_id) AS ReferencedTableName,      
        COL_NAME(referenced_object_id, referenced_column_id) as ReferencedColumnName   
FROM
        sys.foreign_key_columns FKC
WHERE
        OBJECT_NAME(referenced_object_id) = '<ReferencedTableName>'
        AND COL_NAME(referenced_object_id, referenced_column_id) = '<ReferencedColumnName>'
ORDER BY 1

How to create a unique constraint?

ALTER TABLE <tablename>
ADD CONSTRAINT <constraintname> UNIQUE NONCLUSTERED (<columnname>)

Pages

Subscribe to RSS - T-SQL