Display All System-Named Constraints in All User Databases

In my previous post, I mentioned that system-named objects get arbitrary names (like, "PK__Executio__05F5D74515DA3E5D"), and that this makes comparing database schemas difficult. To fix this problem, you have to rename the offending constraints, and to do that, you have to find them. So, this script displays all the system-named constraints in all the databases.

-- Display all the system-named constraints in all user databases. 
DECLARE @Sql NVARCHAR(MAX) 

SET @Sql = 'USE [?] '                                                                     + CHAR(10)
         +                                                                                  CHAR(10)
         + 'IF DB_ID() > 4 '                                                              + CHAR(10)     
         + '   AND EXISTS (SELECT * FROM sys.key_constraints WHERE is_system_named = 1) ' + CHAR(10)    
         +                                                                                  CHAR(10)
         + '    SELECT ''?''                           AS ''Database'' '                  + CHAR(10)
         + '         , OBJECT_NAME(parent_object_id)   AS ''Table'' '                     + CHAR(10)
         + '         , name                            AS ''Constraint'' '                + CHAR(10)
         + '      FROM sys.key_constraints '                                              + CHAR(10)
         + '     WHERE is_system_named = 1'
         
PRINT @Sql
EXEC sp_MSforeachdb @sql

The results will look something like this:

Database        Table                  Constraint 
ReportServer    ExecutionLogStorage    PK__Executio__05F5D74515DA3E5D 

Script to Drop and Re-Create Column Statistics

If you're like me, you use a third-party tool to compare SQL Server database schemas. No matter how hard we try to keep schemas in synch, some little change always creeps in, and being able to confirm that two database schemas are exactly alike helps us sleep at night.

Schemas can differ in important ways (who added that stored procedure?) or in seemingly trivial ways (okay, there's an extra space at the end of the column, I can live with that). One difference that may seem trivial, but isn't, is statistics, specifically column statistics. I learned that the hard way years ago, when I published an upgrade script that tried to drop a column from a table: the script failed for some users, because that column happened to have a statistics object on it. Turns out you can't drop a column with statistics: you have to drop the statistics first. Didn't know that.

Another annoying thing about statistics is that if the system creates them, they'll end up with random (sorry, arbitrary) names, and of course the names will be different between databases. This becomes an issue when you're trying to prove that two schemas are the same, and the screen is cluttered with these bogus differences. Yeah, I could disable checking statistics names entirely in my third-party tool, but that's just... No.

So, here's a script that will drop all the column statistics on all the tables in the current database, and then manually re-creates them. Not something you'd want to do on a production machine during peak hours, but very handy in a development environment.

-- Drop all column statistics on all tables.  We join sys.tables to ensure 
-- these are user (not system) tables.  Note that statistics for indexes 
-- cannot be dropped; the index itself has to be dropped to get rid of its 
-- statistics. 
DECLARE @Sql        NVARCHAR(MAX)       SET @Sql       = ''
DECLARE @TableName  sysname             SET @TableName = ''
DECLARE @StatsName  sysname             SET @StatsName = ''

DECLARE cur CURSOR LOCAL FOR
SELECT OBJECT_NAME(s.object_id)   AS 'TableName'
     , s.name                     AS 'StatsName'
  FROM sys.stats     s 
  JOIN sys.tables    t
    ON s.object_id = t.object_id
 WHERE s.object_id > 100
   AND s.name NOT IN 
         (SELECT name FROM sys.indexes WHERE object_id = s.object_id)

OPEN cur
FETCH NEXT FROM cur INTO @TableName, @StatsName

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @Sql = 'DROP STATISTICS ' + QUOTENAME(@TableName) + '.' + QUOTENAME(@StatsName)
    PRINT @Sql
    EXEC sp_executesql @Sql
    FETCH NEXT FROM cur INTO @TableName, @StatsName
END

CLOSE cur 
DEALLOCATE cur 

-- Create the statistics for all tables and columns in the database that don't 
-- already exist.  This is much easier than using CREATE STATISTICS on each 
-- table, as you can only do 16 columns at a time, and have to name them each. 
RAISERROR('Creating statistics on all tables and columns that are missing them', 10, 1) WITH NOWAIT, LOG
EXEC sp_createstats @indexonly = 'NO', @fullscan = 'FULLSCAN', @norecompute ='NO'

-- Set the automatic UPDATE STATISTICS setting to 'ON' for all indexes and 
-- statistics for all tables and indexed views in the database. 
RAISERROR('Running sp_autostats for all tables...', 10, 1) WITH NOWAIT, LOG
EXEC sp_MSforeachtable '  PRINT ''?''   EXEC sp_autostats ''?'', @flagc = ''ON'' '

-- Display the new names of the column indexes. 
SELECT OBJECT_NAME(s.object_id)   AS 'TableName'
     , s.name                     AS 'StatsName'
  FROM sys.stats     s 
  JOIN sys.tables    t
    ON s.object_id = t.object_id
 WHERE s.object_id > 100
   AND s.name NOT IN 
         (SELECT name FROM sys.indexes WHERE object_id = s.object_id)

What can you expect from this script? Well, I ran this against my local ReportServer database (I'm not recommending that you do that, mind you), and got the following results:

DROP STATISTICS [ConfigurationInfo].[_WA_Sys_00000003_0425A276]
DROP STATISTICS [Catalog].[_WA_Sys_00000014_060DEAE8]
DROP STATISTICS [Users].[_WA_Sys_00000004_1273C1CD]
DROP STATISTICS [Users].[_WA_Sys_00000003_1273C1CD]
DROP STATISTICS [Users].[_WA_Sys_00000005_1273C1CD]
DROP STATISTICS [Policies].[_WA_Sys_00000002_173876EA]
DROP STATISTICS [ModelItemPolicy].[_WA_Sys_00000004_1920BF5C]
DROP STATISTICS [SecData].[_WA_Sys_00000003_1B0907CE]
DROP STATISTICS [Event].[_WA_Sys_00000007_2A4B4B5E]
DROP STATISTICS [Event].[_WA_Sys_00000006_2A4B4B5E]
DROP STATISTICS [Subscriptions].[_WA_Sys_00000010_2D27B809]
DROP STATISTICS [SnapshotData].[_WA_Sys_00000009_34C8D9D1]
DROP STATISTICS [SnapshotData].[_WA_Sys_0000000A_34C8D9D1]
DROP STATISTICS [ChunkData].[_WA_Sys_00000005_36B12243]
DROP STATISTICS [ChunkData].[_WA_Sys_00000004_36B12243]
DROP STATISTICS [Notifications].[_WA_Sys_00000012_3A81B327]
DROP STATISTICS [Notifications].[_WA_Sys_0000000C_3A81B327]
DROP STATISTICS [Notifications].[_WA_Sys_00000011_3A81B327]
DROP STATISTICS [Notifications].[_WA_Sys_0000000F_3A81B327]
DROP STATISTICS [Notifications].[_WA_Sys_00000004_3A81B327]
DROP STATISTICS [RunningJobs].[_WA_Sys_0000000A_48CFD27E]
DROP STATISTICS [RunningJobs].[_WA_Sys_00000006_48CFD27E]
DROP STATISTICS [RunningJobs].[_WA_Sys_00000008_48CFD27E]
DROP STATISTICS [RunningJobs].[_WA_Sys_00000002_48CFD27E]
DROP STATISTICS [DBUpgradeHistory].[_WA_Sys_00000002_731B1205]
DROP STATISTICS [Keys].[_WA_Sys_00000004_7E6CC920]
DROP STATISTICS [Keys].[_WA_Sys_00000006_7E6CC920]
Creating statistics on all tables and columns that are missing them
Table 'ReportServer.dbo.History': Creating statistics for the following columns:
     SnapshotDate
Table 'ReportServer.dbo.ConfigurationInfo': Creating statistics for the following columns:
     Value
Table 'ReportServer.dbo.Catalog': Creating statistics for the following columns:
     Name
     Content
     Intermediate
     Property
     Description
     Hidden
     CreatedByID
     CreationDate
     ModifiedByID
     ModifiedDate
     MimeType
     SnapshotLimit
     Parameter
     PolicyID
     PolicyRoot
     ExecutionFlag
     ExecutionTime
     SubType
     ComponentID
Table 'ReportServer.dbo.UpgradeInfo': Creating statistics for the following columns:
     Status
Table 'ReportServer.dbo.SubscriptionsBeingDeleted': Creating statistics for the following columns:
     CreationDate
Table 'ReportServer.dbo.ModelDrill': Creating statistics for the following columns:
     ReportID
     ModelItemID
     Type
Table 'ReportServer.dbo.Segment': Creating statistics for the following columns:
     Content
Table 'ReportServer.dbo.ChunkSegmentMapping': Creating statistics for the following columns:
     StartByte
     LogicalByteCount
     ActualByteCount
Table 'ReportServer.dbo.ModelPerspective': Creating statistics for the following columns:
     ID
     PerspectiveID
     PerspectiveName
     PerspectiveDescription
Table 'ReportServer.dbo.CachePolicy': Creating statistics for the following columns:
     ExpirationFlags
     CacheExpiration
Table 'ReportServer.dbo.SegmentedChunk': Creating statistics for the following columns:
     ChunkFlags
     ChunkName
     ChunkType
     Version
     MimeType
Table 'ReportServer.dbo.Users': Creating statistics for the following columns:
     UserType
     AuthType
     UserName
Table 'ReportServer.dbo.ExecutionLogStorage': Creating statistics for the following columns:
     InstanceName
     ReportID
     UserName
     ExecutionId
     RequestType
     Format
     Parameters
     ReportAction
     TimeEnd
     TimeDataRetrieval
     TimeProcessing
     TimeRendering
     Source
     Status
     ByteCount
     RowCount
Table 'ReportServer.dbo.DataSource': Creating statistics for the following columns:
     Name
     Extension
     Link
     CredentialRetrieval
     Prompt
     ConnectionString
     OriginalConnectionString
     OriginalConnectStringExpressionBased
     UserName
     Password
     Flags
     Version
Table 'ReportServer.dbo.Policies': Creating statistics for the following columns:
     PolicyFlag
Table 'ReportServer.dbo.ModelItemPolicy': Creating statistics for the following columns:
     ModelItemID
     PolicyID
Table 'ReportServer.dbo.SecData': Creating statistics for the following columns:
     AuthType
     XmlDescription
     NtSecDescPrimary
     NtSecDescSecondary
Table 'ReportServer.dbo.Roles': Creating statistics for the following columns:
     Description
     TaskMask
     RoleFlags
Table 'ReportServer.dbo.PolicyUserRole': Creating statistics for the following columns:
     UserID
     PolicyID
Table 'ReportServer.dbo.Event': Creating statistics for the following columns:
     EventType
     EventData
     ProcessHeartbeat
     BatchID
Table 'ReportServer.dbo.Subscriptions': Creating statistics for the following columns:
     OwnerID
     Report_OID
     Locale
     InactiveFlags
     ExtensionSettings
     ModifiedByID
     ModifiedDate
     Description
     LastStatus
     EventType
     MatchData
     LastRunTime
     Parameters
     DataSettings
     DeliveryExtension
     Version
     ReportZone
Table 'ReportServer.dbo.ActiveSubscriptions': Creating statistics for the following columns:
     SubscriptionID
     TotalNotifications
     TotalSuccesses
     TotalFailures
Table 'ReportServer.dbo.SnapshotData': Creating statistics for the following columns:
     CreatedDate
     ParamsHash
     QueryParams
     EffectiveParams
     Description
     DependsOnUser
     TransientRefcount
     ExpirationDate
     PageCount
     HasDocMap
     PaginationMode
     ProcessingFlags
Table 'ReportServer.dbo.ChunkData': Creating statistics for the following columns:
     ChunkFlags
     ChunkName
     ChunkType
     Version
     MimeType
     Content
Table 'ReportServer.dbo.Notifications': Creating statistics for the following columns:
     SubscriptionID
     ActivationID
     ReportID
     SnapShotDate
     ExtensionSettings
     Locale
     Parameters
     Attempt
     SubscriptionLastRunTime
     DeliveryExtension
     SubscriptionOwnerID
     IsDataDriven
     BatchID
     ProcessHeartbeat
     Version
     ReportZone
Table 'ReportServer.dbo.Batch': Creating statistics for the following columns:
     Action
     Item
     Parent
     Param
     BoolParam
     Content
     Properties
Table 'ReportServer.dbo.Schedule': Creating statistics for the following columns:
     StartDate
     Flags
     NextRunTime
     LastRunTime
     EndDate
     RecurrenceType
     MinutesInterval
     DaysInterval
     WeeksInterval
     DaysOfWeek
     DaysOfMonth
     Month
     MonthlyWeek
     State
     LastRunStatus
     ScheduledRunTimeout
     CreatedById
     EventType
     EventData
     Type
     ConsistancyCheck
     Path
Table 'ReportServer.dbo.ReportSchedule': Creating statistics for the following columns:
     ReportAction
Table 'ReportServer.dbo.RunningJobs': Creating statistics for the following columns:
     StartDate
     RequestName
     RequestPath
     UserId
     Description
     Timeout
     JobAction
     JobType
     JobStatus
Table 'ReportServer.dbo.ServerParametersInstance': Creating statistics for the following columns:
     ParentID
     Path
     CreateDate
     ModifiedDate
     Timeout
     ParametersValues
Table 'ReportServer.dbo.DBUpgradeHistory': Creating statistics for the following columns:
     DbVersion
     User
     DateTime
Table 'ReportServer.sys.queue_messages_1977058079': Creating statistics for the following columns:
     priority
     queuing_order
     conversation_group_id
     conversation_handle
     message_sequence_number
     message_id
     message_type_id
     service_id
     service_contract_id
     validation
     next_fragment
     fragment_size
     fragment_bitmap
     binary_message_body
Table 'ReportServer.sys.queue_messages_2009058193': Creating statistics for the following columns:
     priority
     queuing_order
     conversation_group_id
     conversation_handle
     message_sequence_number
     message_id
     message_type_id
     service_id
     service_contract_id
     validation
     next_fragment
     fragment_size
     fragment_bitmap
     binary_message_body
Table 'ReportServer.dbo.DataSets': Creating statistics for the following columns:
     Name
Table 'ReportServer.sys.queue_messages_2041058307': Creating statistics for the following columns:
     priority
     queuing_order
     conversation_group_id
     conversation_handle
     message_sequence_number
     message_id
     message_type_id
     service_id
     service_contract_id
     validation
     next_fragment
     fragment_size
     fragment_bitmap
     binary_message_body
Table 'ReportServer.sys.filestream_tombstone_2073058421': Creating statistics for the following columns:
     oplsn_bOffset
     oplsn_slotid
     rowset_guid
     column_guid
     filestream_value_name
     transaction_sequence_num
     status
Table 'ReportServer.sys.syscommittab': Creating statistics for the following columns:
     commit_lbn
     commit_csn
     commit_time
     dbfragid
Table 'ReportServer.dbo.Keys': Creating statistics for the following columns:
     MachineName
     InstanceName
     Client
     PublicKey
     SymmetricKey
Table 'ReportServer.dbo.ServerUpgradeHistory': Creating statistics for the following columns:
     ServerVersion
     User
     DateTime
 
Statistics have been created for the 253 listed columns of the above tables.
Running sp_autostats for all tables...
[dbo].[History]
[dbo].[ConfigurationInfo]
[dbo].[Catalog]
[dbo].[UpgradeInfo]
[dbo].[SubscriptionsBeingDeleted]
[dbo].[ModelDrill]
[dbo].[Segment]
[dbo].[ChunkSegmentMapping]
[dbo].[ModelPerspective]
[dbo].[CachePolicy]
[dbo].[SegmentedChunk]
[dbo].[Users]
[dbo].[ExecutionLogStorage]
[dbo].[DataSource]
[dbo].[Policies]
[dbo].[ModelItemPolicy]
[dbo].[SecData]
[dbo].[Roles]
[dbo].[PolicyUserRole]
[dbo].[Event]
[dbo].[Subscriptions]
[dbo].[ActiveSubscriptions]
[dbo].[SnapshotData]
[dbo].[ChunkData]
[dbo].[Notifications]
[dbo].[Batch]
[dbo].[Schedule]
[dbo].[ReportSchedule]
[dbo].[RunningJobs]
[dbo].[ServerParametersInstance]
[dbo].[DBUpgradeHistory]
[dbo].[DataSets]
[dbo].[Keys]
[dbo].[ServerUpgradeHistory]

TableName          StatsName
History                  SnapshotDate
ConfigurationInfo        Value
Catalog        Name
Catalog        Content
Catalog        Intermediate
Catalog        Property
Catalog        Description
Catalog        Hidden
Catalog        CreatedByID
Catalog        CreationDate
Catalog        ModifiedByID
Catalog        ModifiedDate
Catalog        MimeType
Catalog        SnapshotLimit
Catalog        Parameter
Catalog        PolicyID
Catalog        PolicyRoot
Catalog        ExecutionFlag
Catalog        ExecutionTime
Catalog        SubType
Catalog        ComponentID
UpgradeInfo        Status
SubscriptionsBeingDeleted        CreationDate
ModelDrill        ReportID
ModelDrill        ModelItemID
ModelDrill        Type
Segment        Content
ChunkSegmentMapping        StartByte
ChunkSegmentMapping        LogicalByteCount
ChunkSegmentMapping        ActualByteCount
ModelPerspective        ID
ModelPerspective        PerspectiveID
ModelPerspective        PerspectiveName
ModelPerspective        PerspectiveDescription
CachePolicy        ExpirationFlags
CachePolicy        CacheExpiration
SegmentedChunk        ChunkFlags
SegmentedChunk        ChunkName
SegmentedChunk        ChunkType
SegmentedChunk        Version
SegmentedChunk        MimeType
Users        UserType
Users        AuthType
Users        UserName
ExecutionLogStorage        InstanceName
ExecutionLogStorage        ReportID
ExecutionLogStorage        UserName
ExecutionLogStorage        ExecutionId
ExecutionLogStorage        RequestType
ExecutionLogStorage        Format
ExecutionLogStorage        Parameters
ExecutionLogStorage        ReportAction
ExecutionLogStorage        TimeEnd
ExecutionLogStorage        TimeDataRetrieval
ExecutionLogStorage        TimeProcessing
ExecutionLogStorage        TimeRendering
ExecutionLogStorage        Source
ExecutionLogStorage        Status
ExecutionLogStorage        ByteCount
ExecutionLogStorage        RowCount
DataSource        Name
DataSource        Extension
DataSource        Link
DataSource        CredentialRetrieval
DataSource        Prompt
DataSource        ConnectionString
DataSource        OriginalConnectionString
DataSource        OriginalConnectStringExpressionBased
DataSource        UserName
DataSource        Password
DataSource        Flags
DataSource        Version
Policies        PolicyFlag
ModelItemPolicy        ModelItemID
ModelItemPolicy        PolicyID
SecData        AuthType
SecData        XmlDescription
SecData        NtSecDescPrimary
SecData        NtSecDescSecondary
Roles        Description
Roles        TaskMask
Roles        RoleFlags
PolicyUserRole        UserID
PolicyUserRole        PolicyID
Event        EventType
Event        EventData
Event        ProcessHeartbeat
Event        BatchID
Subscriptions        OwnerID
Subscriptions        Report_OID
Subscriptions        Locale
Subscriptions        InactiveFlags
Subscriptions        ExtensionSettings
Subscriptions        ModifiedByID
Subscriptions        ModifiedDate
Subscriptions        Description
Subscriptions        LastStatus
Subscriptions        EventType
Subscriptions        MatchData
Subscriptions        LastRunTime
Subscriptions        Parameters
Subscriptions        DataSettings
Subscriptions        DeliveryExtension
Subscriptions        Version
Subscriptions        ReportZone
ActiveSubscriptions        SubscriptionID
ActiveSubscriptions        TotalNotifications
ActiveSubscriptions        TotalSuccesses
ActiveSubscriptions        TotalFailures
SnapshotData        CreatedDate
SnapshotData        ParamsHash
SnapshotData        QueryParams
SnapshotData        EffectiveParams
SnapshotData        Description
SnapshotData        DependsOnUser
SnapshotData        TransientRefcount
SnapshotData        ExpirationDate
SnapshotData        PageCount
SnapshotData        HasDocMap
SnapshotData        PaginationMode
SnapshotData        ProcessingFlags
ChunkData        ChunkFlags
ChunkData        ChunkName
ChunkData        ChunkType
ChunkData        Version
ChunkData        MimeType
ChunkData        Content
Notifications        SubscriptionID
Notifications        ActivationID
Notifications        ReportID
Notifications        SnapShotDate
Notifications        ExtensionSettings
Notifications        Locale
Notifications        Parameters
Notifications        Attempt
Notifications        SubscriptionLastRunTime
Notifications        DeliveryExtension
Notifications        SubscriptionOwnerID
Notifications        IsDataDriven
Notifications        BatchID
Notifications        ProcessHeartbeat
Notifications        Version
Notifications        ReportZone
Batch        Action
Batch        Item
Batch        Parent
Batch        Param
Batch        BoolParam
Batch        Content
Batch        Properties
Schedule        StartDate
Schedule        Flags
Schedule        NextRunTime
Schedule        LastRunTime
Schedule        EndDate
Schedule        RecurrenceType
Schedule        MinutesInterval
Schedule        DaysInterval
Schedule        WeeksInterval
Schedule        DaysOfWeek
Schedule        DaysOfMonth
Schedule        Month
Schedule        MonthlyWeek
Schedule        State
Schedule        LastRunStatus
Schedule        ScheduledRunTimeout
Schedule        CreatedById
Schedule        EventType
Schedule        EventData
Schedule        Type
Schedule        ConsistancyCheck
Schedule        Path
RunningJobs        StartDate
RunningJobs        RequestName
RunningJobs        RequestPath
RunningJobs        UserId
RunningJobs        Description
RunningJobs        Timeout
RunningJobs        JobAction
RunningJobs        JobType
RunningJobs        JobStatus
ServerParametersInstance        ParentID
ServerParametersInstance        Path
ServerParametersInstance        CreateDate
ServerParametersInstance        ModifiedDate
ServerParametersInstance        Timeout
ServerParametersInstance        ParametersValues
DBUpgradeHistory        DbVersion
DBUpgradeHistory        User
DBUpgradeHistory        DateTime
DataSets        Name
Keys        MachineName
Keys        InstanceName
Keys        Client
Keys        PublicKey
Keys        SymmetricKey
ServerUpgradeHistory        ServerVersion
ServerUpgradeHistory        User
ServerUpgradeHistory        DateTime

Note that the arbitrarily named column statistics such as [ConfigurationInfo].[_WA_Sys_00000003_0425A276] got renamed to the more standard (and thus, more easily-compared) name [ConfigurationInfo].[Value].