SET NOCOUNT ON;
DECLARE @TempStats TABLE (StatsName varchar(500));
DECLARE @StatsName varchar(500);
DECLARE @sql varchar(MAX);
INSERT INTO @TempStats
(StatsName)
SELECT
OBJECT_NAME([object_id]) + '.' + [name]
FROM
sys.stats S
WHERE
OBJECTPROPERTY([object_id], 'IsMSShipped') = 0 AND
[NAME] LIKE '_WA_Sys%';
SELECT
@StatsName = MIN(StatsName)
FROM
@TempStats;
WHILE @StatsName IS NOT NULL
BEGIN
SELECT @sql = 'DROP STATISTICS ' + @StatsName;
EXEC (@sql);
PRINT 'Deleted statistics: ' + @StatsName;
DELETE
@TempStats
WHERE
StatsName = @StatsName;
SELECT
@StatsName = MIN(StatsName)
FROM
@TempStats;
END
DECLARE @codesnippet varchar(100);
SET @codesnippet = 'EnterStringHere';
SELECT
O.type_desc AS ObjectType,
(LEFT(OBJECT_NAME(M.[object_id]), 50)) AS ObjectName
FROM
sys.sql_modules M
JOIN
sys.objects O ON M.[object_id] = O.[object_id]
WHERE
definition LIKE '%' + @codesnippet + '%'
AND
OBJECTPROPERTY(M.[object_id], 'IsMSShipped') = 0
ORDER BY
ObjectName;
SELECT
DB_NAME(IXStats.database_id) AS DatabaseName,
OBJECT_NAME (IXStats.[object_id]) AS TabellenName,
SIX.[Name] AS IndexName,
IXStats.avg_fragmentation_in_percent,
IXStats.index_type_desc
FROM
sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) IXStats
JOIN
sys.indexes SIX ON IXStats.[object_id] = SIX.[object_id] AND IXStats.index_id = SIX.index_id
WHERE
IXStats.database_id = DB_ID()
ORDER BY
IXStats.avg_fragmentation_in_percent DESC
DECLARE @string varchar(500)
SELECT
@string = '%SomeString%'
SELECT
SCP.usecounts, SCP.cacheobjtype, SCP.objtype,
[Statement].text,
Executionplan.query_plan
FROM
sys.dm_exec_cached_plans SCP
OUTER APPLY sys.dm_exec_sql_text(plan_handle) [Statement]
OUTER APPLY sys.dm_exec_query_plan(plan_handle) Executionplan
WHERE
Statement.[text] LIKE @string AND
SCP.cacheobjtype ='Compiled Plan' AND
SCP.objtype = 'proc'
SELECT *
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = 'FUNCTION'
AND OBJECTPROPERTY(OBJECT_ID(QUOTENAME(SPECIFIC_SCHEMA) + '.' +
QUOTENAME(SPECIFIC_NAME)),'IsMSShipped') = 0
oder alternativ dazu:
SELECT *
FROM sys.sysobjects -- oder sys.objects
WHERE type IN ('FN', 'IF', 'TF')
SELECT *
FROM sys.sql_modules
WHERE OBJECTPROPERTY(OBJECT_ID,'IsScalarFunction') = 1
OR OBJECTPROPERTY(OBJECT_ID,'IsTableFunction') = 1
OR OBJECTPROPERTY(OBJECT_ID,'IsInlineFunction') = 1
DECLARE @seconds INT
DECLARE @hours INT
DECLARE @minutes INT
--
SET @seconds = 3661
SET @hours = FLOOR(@seconds/(60*60))
SET @minutes = FLOOR((@seconds - (@hours*3600))/60)
--
SELECT
RIGHT('0' + CONVERT(VARCHAR(2), @hours), 2) +
':' +
RIGHT('0' + CONVERT(VARCHAR(2), @minutes), 2)
-----
01:01
(1 row(s) affected)
Man könnte allerdings auch:
SET @secs = 3661
SELECT CONVERT(VARCHAR(5), DATEADD(SECOND, @secs, '00:00'), 108)
-----
01:01
(1 row(s) affected)
verwenden.
Das nachfolgende Skript löscht alle existierenden Einschränkungen für die angegebene Tabelle. Will man alle Einschränkungen der aktuellen Datenbank löschen, muss man einfach nur die WHERE Zeile auskommentieren oder entfernen.
USE PUBS GO SET NOCOUNT ON CREATE TABLE #TableSpace ( Name char(20) ,RowCnt int ,Reserved varchar(15) ,Data varchar(15) ,Index_Size varchar(15) ,Unused varchar(15) ) DECLARE @Table sysname DECLARE TableCur CURSOR FOR SELECT Table_Name FROM INFORMATION_SCHEMA.Tables WHERE Table_Type = 'BASE TABLE' AND OBJECTPROPERTY(OBJECT_ID(Table_Name),'IsMSShipped') = 0 OPEN TableCur FETCH NEXT FROM TableCur INTO @Table WHILE @@FETCH_STATUS = 0 BEGIN INSERT #TableSpace EXEC sp_spaceused @Table FETCH NEXT FROM TableCur INTO @Table END CLOSE TableCur DEALLOCATE TableCur SELECT * FROM #TableSpace DROP TABLE #TableSpace SET NOCOUNT OFF Name RowCnt Reserved Data Index_Size Unused -------------------- ----------- --------------- --------------- --------------- --------------- __tmpTBLCOL 131 80 KB 16 KB 8 KB 56 KB authors 23 40 KB 8 KB 32 KB 0 KB discounts 3 16 KB 8 KB 8 KB 0 KB dup_authors 40 80 KB 16 KB 8 KB 56 KB employee 43 40 KB 8 KB 32 KB 0 KB jobs 14 24 KB 8 KB 16 KB 0 KB max_t 4 16 KB 8 KB 8 KB 0 KB median 8 16 KB 8 KB 8 KB 0 KB pub_info 8 160 KB 120 KB 16 KB 24 KB publishers 8 24 KB 8 KB 16 KB 0 KB roysched 86 32 KB 8 KB 24 KB 0 KB sales 21 56 KB 8 KB 48 KB 0 KB silly_one 0 0 KB 0 KB 0 KB 0 KB stores 6 24 KB 8 KB 16 KB 0 KB tableCounts 15 16 KB 8 KB 8 KB 0 KB titleauthor 25 56 KB 8 KB 48 KB 0 KB titles 18 40 KB 8 KB 32 KB 0 KB Trace_Table_Name 0 0 KB 0 KB 0 KB 0 KB vals 127 24 KB 8 KB 16 KB 0 KB x 2 16 KB 8 KB 8 KB 0 KB
Danke an Jonathan van Houtte für das Originalskript.
Um sicherzustellen, dass RowCnt aktuell ist, sollte man vorher
DBCC UPDATEUSAGE(0) WITH COUNT_ROWS
ausführen.