Frank Kalis

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.

Ganze Geschichte »

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.