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].
1 comments:
Thank you so much helped alot..
Post a Comment