Tag: "just code"
Zahlen eines Integers aufaddieren
Okt 17th
DECLARE @i int, @l int; SELECT @i = 12, @l = LEN(@i); SELECT SUM(x.Digit) FROM (SELECT CAST(SUBSTRING(RTRIM(@i), N.Number, 1) AS int) AS Digit FROM dbo.Number N WHERE N.Number BETWEEN 1 AND… mehr »
Tabellen ohne Indizes
Aug 24th
Hier sind diverse Möglichkeiten um zu überprüfen, für welche Tabellen kein Clustered Index definiert wurde: mehr »
Alle SQL-Server im Netzwerk anzeigen
Jan 12th
USE AdventureWorks;
GO
EXEC xp_cmdshell 'SQLCMD -L c';
GO mehr »
Fremdschlüssel Aktion Check
Dez 29th
SELECT
OBJECTPROPERTY(O.[OBJECT_ID], 'CnstIsUpdateCascade') AS updcascade,
OBJECTPROPERTY(O.[OBJECT_ID], 'CnstIsDeleteCascade') AS delcascade,
[name]
FROM
sys.objects O
WHERE
OBJECTPROPERTY(O.[OBJECT_ID], 'IsForeignKey') = 1 AND… mehr »
Durchschnittliche Zeilenlänge anzeigen
Dez 29th
DECLARE @TableID int
SELECT @TableID = OBJECT_ID ('Enter TableName here')
SELECT
OBJECT_NAME (IXStats.[object_id]) AS ObjectName,
IXStats.index_type_desc,
IXStats.avg_record_size_in_bytes
FROM
sys.dm_db_index_physical_stats (DB_ID(),… mehr »
Feststellen, ob eine Tabelle einen Trigger besitzt
Dez 11th
CodeSELECT CASE WHEN OBJECTPROPERTY(OBJECT_ID('dbo.BookSource'), 'HasAfterTrigger') + OBJECTPROPERTY(OBJECT_ID('dbo.BookSource'), 'HasInsteadOfTrigger') > 0… mehr »
Informationen zu Trigger
Dez 4th
SELECT
T.[name] AS Trigger_Name,
OBJECT_NAME(T.parent_id) AS Table_Name,
TE.type_desc,
T.create_date
FROM
sys.triggers T
JOIN
sys.trigger_events TE ON T.[object_id] = TE.[object_id]; mehr »
Objektberechtigungen anzeigen
Okt 21st
DECLARE @string sysname; SELECT @string = '< search string >'; SELECT OBJECT_NAME(major_id) AS ObjectName, USER_NAME(grantor_principal_id) AS Grantor, USER_NAME(grantee_principal_id) AS Grantee, p.permission_name FROM… mehr »
WA_Sys Statistiken löschen
Sep 23rd
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… mehr »
Zeichenfolge im SQL Code finden
Sep 22nd
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] =… mehr »
Indexfragmentierung anzeigen
Sep 1st
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… mehr »
Plan Reuse abfragen
Aug 29th
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… mehr »
Wie kann ich Funktionen in SQL Server 2005 identifizieren?
Aug 31st
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… mehr »
Minuten in Stunden umwandeln
Jun 19th
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' +… mehr »
Foreign Key Constraints löschen
Nov 26th
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. mehr »
sp_spaceused in Tabelle unleiten
Jul 29th
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… mehr »
Zeitwerte addieren
Jul 13th
SET NOCOUNT ON
CREATE TABLE #temp_table
(
hm VARCHAR(5)
)
INSERT #temp_table VALUES('8:30')
INSERT #temp_table VALUES('5:01')
INSERT #temp_table VALUES('12:59')
INSERT #temp_table VALUES('7:20')
SET NOCOUNT OFF
SELECT
CONVERT(VARCHAR,… mehr »
Nicht-alphanumerische Zeichen finden
Jul 13th
SET NOCOUNT ON CREATE TABLE strip_t ( col VARCHAR(30) ) INSERT INTO strip_t VALUES ('Frank') INSERT INTO strip_t VALUES ('Frank!') INSERT INTO strip_t VALUES ('Fr^ank') INSERT INTO strip_t VALUES ('Fran&k') INSERT INTO strip_t VALUES ('Fran$k')… mehr »
Integer in Zeit umwandeln
Jul 13th
DECLARE @time INT SET @time = 7382 SELECT @time / 3600 AS Stunden , (@time / 60)% 60 AS Minuten , @time % 60 AS Sekunden Stunden Minuten Sekunden ----------- ----------- ----------- 2 3 2 (1 row(s)… mehr »
Datum aus einer DATETIME Spalte entfernen
Jul 13th
SELECT RIGHT(STUFF(CONVERT(CHAR(22),GETDATE(),13),21,4,' '),9)
---------
10:14:40
(1 row(s) affected)
--Oder
SELECT CONVERT(CHAR(8), GETDATE(), 8)
--------
10:14:40
(1 row(s) affected)
--Oder
SELECT CONVERT(CHAR(8),… mehr »
Summe pro Woche anzeigen
Jul 13th
USE Northwind
GO
SELECT
n.dow
, ISNULL(SUM(o.Freight),0)
FROM Orders o
RIGHT JOIN
(SELECT 1
dow
UNION ALL SELECT 2
UNION ALL SELECT 3
UNION ALL SELECT 4
UNION ALL SELECT 5
UNION ALL SELECT 6… mehr »
Konvertierung aus Julianischem Kalender
Jul 13th
DECLARE @yr INT DECLARE @jd INT SELECT @yr = 2002, @jd = 245 SELECT DATEADD(yyyy, @yr - 1900, -1) + @jd AS Julian_Date Julian_Date ------------------------------------------------------ 2002-09-02… mehr »
Sekunden in Minuten umwandeln
Jul 13th
DECLARE @sekunden INT SET @sekunden = 61 SELECT CONVERT(VARCHAR(2), FLOOR(@sekunden/60)) + ':' + RIGHT('0' + CONVERT(VARCHAR(2), (@sekunden%60)), 2) ----- 1:01 (1 row(s) affected) mehr »
Laufende Summe in T-SQL
Jul 13th
IF OBJECT_ID('lfdsum_t') IS NOT NULL
DROP TABLE lfdsum_t
GO
CREATE TABLE lfdsum_t (col1 int)
INSERT INTO lfdsum_t values (1);
INSERT INTO lfdsum_t values (2);
INSERT INTO lfdsum_t values (3);
INSERT INTO lfdsum_t values (4);
INSERT INTO lfdsum_t… mehr »
DATETIME Spalten gruppieren und sortieren
Jul 13th
SELECT
DATEADD(day, 0 , DATEDIFF(day, 0, MsgDateReceived)) AS "Datum"
, COUNT(*) AS Anzahl
FROM mails_header
GROUP BY
DATEADD(day, 0 , DATEDIFF(day, 0, MsgDateReceived))
ORDER BY
"Datum" DESC mehr »
