How to revalidate/refresh all stored procedures and functions

After a stored procedure is created, you might make changes to the underlying objects that could break the stored procedure. The script presented will retrieve the list of stored procedures and functions and then will re-validate them, based on sp_refreshsqlmodule system stored procedure.

Here is the script that performs this.

SET NOCOUNT ON;

DECLARE
        @RowIndex AS SMALLINT = 1
        , @ObjectName AS NVARCHAR(128)
        , @Sql AS NVARCHAR(MAX)
       

-- Get list of objects (stored procedures, functions) to be refreshed
DECLARE @ObjectsTable AS Table (RowIndex SMALLINT IDENTITY(1,1), ObjectType nvarchar(60), ObjectName NVARCHAR(128), IsRefreshed BIT, Error NVARCHAR(MAX))
INSERT INTO @ObjectsTable (ObjectType, ObjectName, IsRefreshed)
SELECT O.type_desc, SCHEMA_NAME(O.schema_id) + '.' + O.name, 1
FROM
        sys.sql_modules M
        INNER JOIN sys.objects O ON O.object_id = M.object_id
WHERE
        O.is_ms_shipped = 0
        AND O.type_desc NOT IN ('VIEW')
ORDER BY O.type_desc, O.name

-- For each function/sp...
WHILE @RowIndex <= (SELECT COUNT(*) FROM @ObjectsTable)
BEGIN  
        SELECT @ObjectName = ObjectName        
        FROM @ObjectsTable
        WHERE RowIndex = @RowIndex
       
        SET @Sql = 'EXEC sp_refreshsqlmodule ''' + @ObjectName + ''''
        BEGIN TRY
                EXEC sp_executesql @Sql
        END TRY
        BEGIN CATCH
                UPDATE @ObjectsTable
                SET
                        IsRefreshed = 0,
                        Error = ERROR_MESSAGE()
                WHERE RowIndex = @RowIndex
               
                IF @@TRANCOUNT > 0
                        ROLLBACK TRAN
        END CATCH
                                                               
        -- Process next object
        SET @RowIndex = @RowIndex + 1
END


-- List all objects not refreshed
SELECT ObjectType, ObjectName, IsRefreshed, Error
FROM @ObjectsTable
WHERE IsRefreshed = 0
GO

Get list of replicated tables

T-SQL script for getting list of replicated tables and associated subscriptions / publications:

USE master;

SELECT
        P.publication,
        S.publisher_db, S.subscriber_db,
        A.source_owner, A.source_object, A.destination_owner, A.destination_object
FROM
        distribution.dbo.MSsubscriptions S
        INNER JOIN distribution.dbo.MSpublications P ON P.publication_id = S.publication_id
        INNER JOIN distribution.dbo.MSarticles A
                ON A.publication_id = P.publication_id AND A.article_id = S.article_id
ORDER BY
        P.publication, S.subscriber_db, A.source_object;

How to find all the dependencies of a table - Object dependencies

In SQL Server 2008 there are two new Dynamic Management Functions introduced to keep track of Object Dependencies: sys.dm_sql_referenced_entities and sys.dm_sql_referencing_entities.

Returning the entities that refer to a given entity:

SELECT
        referencing_schema_name, referencing_entity_name,
        referencing_class_desc, is_caller_dependent
FROM sys.dm_sql_referencing_entities ('<TableName>', 'OBJECT')

Returning entities that are referenced by an object:

SELECT
        referenced_schema_name, referenced_entity_name, referenced_minor_name,
        referenced_class_desc, is_caller_dependent, is_ambiguous
FROM sys.dm_sql_referenced_entities ('<StoredProcedureName>', 'OBJECT');

Another option is to use a pretty useful tool called SQL Dependency Tracker from Red Gate.

Get the space used for each database

SELECT
        DB.name,
        MF.physical_name,
        MF.type_desc AS FileType,
        MF.size * 8 / 1024 AS FileSizeMB,
        fileproperty(MF.name, 'SpaceUsed') * 8/ 1024 AS UsedSpaceMB
FROM
        sys.master_files MF
        JOIN sys.databases DB ON DB.database_id = MF.database_id
ORDER BY
        DB.name,
        FileSizeMB DESC

EXEC sp_msforeachdb @command1='use [?] exec sp_spaceused'

USE <DatabaseName>
EXEC sp_spaceused

Using DBCC SQLPERF to check log free space for a SQL Server database:

DBCC SQLPERF(logspace)

T-SQL script for copying a database

USE master;

DECLARE
        @SourceDatabaseName AS SYSNAME = '<SourceDatabaseName>',
        @TargetDatabaseName AS SYSNAME = '<NewDatabaseName>'


       
-- ============================================
-- Define path where backup will be saved
-- ============================================
IF NOT EXISTS (SELECT 1 FROM sys.databases WHERE name = @SourceDatabaseName)
        RAISERROR ('Variable @SourceDatabaseName is not set correctly !', 20, 1) WITH LOG      
       
DECLARE @SourceBackupFilePath varchar(2000)
SELECT @SourceBackupFilePath = BMF.physical_device_name
FROM
        msdb.dbo.backupset B
        JOIN msdb.dbo.backupmediafamily BMF ON B.media_set_id = BMF.media_set_id
WHERE B.database_name = @SourceDatabaseName
ORDER BY B.backup_finish_date DESC

SET @SourceBackupFilePath = REPLACE(@SourceBackupFilePath, '.bak', '_clone.bak')



-- ============================================
-- Backup source database
-- ============================================
DECLARE @Sql NVARCHAR(MAX)
SET @Sql = 'BACKUP DATABASE @SourceDatabaseName TO DISK = ''@SourceBackupFilePath'''
SET @Sql = REPLACE(@Sql, '@SourceDatabaseName', @SourceDatabaseName)
SET @Sql = REPLACE(@Sql, '@SourceBackupFilePath', @SourceBackupFilePath)
SELECT 'Performing backup...', @Sql as ExecutedSql
EXEC (@Sql)



-- ============================================
-- Automatically compose database files (.mdf and .ldf) paths
-- ============================================
DECLARE
        @LogicalDataFileName as NVARCHAR(MAX),
        @LogicalLogFileName as NVARCHAR(MAX),
        @TargetDataFilePath as NVARCHAR(MAX),
        @TargetLogFilePath as NVARCHAR(MAX)
               
SELECT
        @LogicalDataFileName = name,
        @TargetDataFilePath = SUBSTRING(physical_name,1,LEN(physical_name)-CHARINDEX('\',REVERSE(physical_name))) + '\' + @TargetDatabaseName + '.mdf'
FROM sys.master_files
WHERE
        database_id = DB_ID(@SourceDatabaseName)       
        AND type = 0            -- datafile file

SELECT
        @LogicalLogFileName = name,
        @TargetLogFilePath = SUBSTRING(physical_name,1,LEN(physical_name)-CHARINDEX('\',REVERSE(physical_name))) + '\' + @TargetDatabaseName + '.ldf'
FROM sys.master_files
WHERE
        database_id = DB_ID(@SourceDatabaseName)       
        AND type = 1            -- log file    



-- ============================================
-- Restore target database
-- ============================================
IF EXISTS (SELECT 1 FROM sys.databases WHERE name = @TargetDatabaseName)
        RAISERROR ('Variable @SourceDatabaseName is not set correctly as a database with the same name already exists!', 20, 1) WITH LOG       
       
SET @Sql = 'RESTORE DATABASE @TargetDatabaseName
FROM DISK = '
'@SourceBackupFilePath''
WITH MOVE '
'@LogicalDataFileName'' TO ''@TargetDataFilePath'',
MOVE '
'@LogicalLogFileName'' TO ''@TargetLogFilePath'''
SET @Sql = REPLACE(@Sql, '@TargetDatabaseName', @TargetDatabaseName)
SET @Sql = REPLACE(@Sql, '@SourceBackupFilePath', @SourceBackupFilePath)
SET @Sql = REPLACE(@Sql, '@LogicalDataFileName', @LogicalDataFileName)
SET @Sql = REPLACE(@Sql, '@TargetDataFilePath', @TargetDataFilePath)
SET @Sql = REPLACE(@Sql, '@LogicalLogFileName', @LogicalLogFileName)
SET @Sql = REPLACE(@Sql, '@TargetLogFilePath', @TargetLogFilePath)
SELECT 'Restoring...', @Sql as ExecutedSql
EXEC (@Sql)

Get database backup history

In case you need to see the last backups performed on a specific database:

SELECT
        BMF.physical_device_name as BackupFileName     
        , B.backup_finish_date as BackupDate
        , B.backup_size/1024.0/1024.0 AS BackupSizeMB
FROM
        msdb.dbo.backupset B
        JOIN msdb.dbo.backupmediafamily BMF ON B.media_set_id = BMF.media_set_id
WHERE database_name = '<YourDatabaseName>'
ORDER BY B.backup_finish_date DESC

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

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.

Pages

Subscribe to SQLhint RSS