Tag: "just code"

WA_Sys Statistiken löschen

Posted on Sep 23, 2008 von in SQL Server
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

Zeichenfolge im SQL Code finden

Posted on Sep 22, 2008 von in SQL Server
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;

Indexfragmentierung anzeigen

Posted on Sep 1, 2008 von in SQL Server
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

Plan Reuse abfragen

Posted on Aug 29, 2008 von in SQL Server
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'

Wie kann ich Funktionen in SQL Server 2005 identifizieren?

Posted on Aug 31, 2007 von in SQL Server

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

Minuten in Stunden umwandeln

Posted on Jun 19, 2006 von in SQL Server
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.

 

Foreign Key Constraints löschen

Posted on Nov 26, 2004 von in SQL Server

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 »

sp_spaceused in Tabelle unleiten

Posted on Jul 29, 2004 von in SQL Server
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.