DEFINITIONS

Definitions More Info.
Definition ID2.034
TitleSQL
CategoryNOTES
Definitionstatistics linked server problem fix
Definition Description-- Cozum olarak procedure yazdim ve uzaktan erisene execute yetkisi verdim.
CREATE PROCEDURE up_linkedserver_statiticsBugFix2
@dbaname varchar(100),
@tablename varchar(100),
@statsname varchar(100)
AS
DECLARE @sql varchar(4000),
@dropstats varchar(250),
@createstats varchar(250)
SELECT DISTINCT
@dropstats='DROP STATISTICS ' + QUOTENAME(S.NAME) +
' ON ' + QUOTENAME(SCHEMA_NAME(obj.schema_id)) +
'.' + QUOTENAME(obj.[name]) +
'(' +
STUFF( ( SELECT ', ' +
QUOTENAME(c.name)

FROM sys.stats_columns sc

INNER JOIN sys.columns c
ON c.[object_id] = sc.[object_id]
AND c.column_id = sc.column_id

WHERE sc.[object_id] = s.[object_id]
AND sc.stats_id = s.stats_id

ORDER BY sc.stats_column_id
FOR XML PATH('')),1 ,1, '') +
')' ,
@createstats='CREATE STATISTICS ' + QUOTENAME(S.NAME) +
' ON ' + QUOTENAME(SCHEMA_NAME(obj.schema_id)) +
'.' + QUOTENAME(obj.[name]) +
'(' +
STUFF( ( SELECT ', ' +
QUOTENAME(c.name)

FROM sys.stats_columns sc

INNER JOIN sys.columns c
ON c.[object_id] = sc.[object_id]
AND c.column_id = sc.column_id

WHERE sc.[object_id] = s.[object_id]
AND sc.stats_id = s.stats_id

ORDER BY sc.stats_column_id
FOR XML PATH('')),1 ,1, '') +
')' +
ISNULL(' WHERE ' + filter_definition,'') +
ISNULL(STUFF (
--ISNULL(',STATS_STREAM = ' + @StatsStream, '') +
CASE WHEN no_recompute = 1 THEN ',NORECOMPUTE' ELSE '' END +
CASE WHEN is_incremental = 1 THEN ',INCREMENTAL=ON' ELSE '' END
, 1 , 1 , ' WITH ' ) , '')

FROM sys.stats s

INNER JOIN sys.partitions par
ON par.[object_id] = s.[object_id]

INNER JOIN sys.objects obj
ON par.[object_id] = obj.[object_id]

WHERE 1=1
AND obj.[name] =@tablename
AND OBJECTPROPERTY(s.OBJECT_ID,'IsUserTable') = 1
AND s.name=@statsname

print (@sql)
print (@dropstats)
print (@createstats)
RecordBycunay
Record Date29-06-2020 15:21:48
Düzenle
Kopyala
Sil