Category Archives: SQL

SQL Server Datenbank Design -> Kroki ER Diagramm

https://kroki.io/examples.html#erd

DECLARE @FlatTable TABLE
(
    TableName NVARCHAR(128),
    ColumnName NVARCHAR(128),
    DataType NVARCHAR(128),
    [MaxLength] INT,
    [Precision] INT,
    Scale INT,
    Nullable BIT,
    [Identity] BIT,
    PrimaryKey BIT,
    ForeignKeyName NVARCHAR(128),
    ParentTable NVARCHAR(128),
    ParentColumn NVARCHAR(128),
    ReferencedTable NVARCHAR(128),
    ReferencedColumn NVARCHAR(128)
);


INSERT INTO @FlatTable
SELECT tbl.[name] AS [TableName],
       col.[name] AS [ColumnName],
       ty.[name] AS [DataType],
       col.[max_length] AS [MaxLength],
       col.[precision] AS [Precision],
       col.[scale] AS [Scale],
       col.[is_nullable] AS [Nullable],
       col.[is_identity] AS [Identity],
       CONVERT(BIT, IIF(pkcol.index_id IS NOT NULL, 1, 0)) AS [PrimaryKey],
       fk.name AS ForeignKeyName,
       OBJECT_NAME(fk.parent_object_id) AS ParentTable,
       COL_NAME(fkc.parent_object_id, fkc.parent_column_id) AS ParentColumn,
       OBJECT_NAME(fk.referenced_object_id) AS ReferencedTable,
       COL_NAME(fkc.referenced_object_id, fkc.referenced_column_id) AS ReferencedColumn
FROM       sys.tables tbl
INNER JOIN sys.columns col
        ON col.[object_id] = tbl.[object_id]
INNER JOIN sys.types ty
        ON ty.[user_type_id] = col.[user_type_id] -- do not use system_type_id
LEFT JOIN (
           SELECT ind.[object_id] AS [table_object_id], ind.[index_id], ind.[column_id]
           FROM   sys.index_columns ind
           INNER JOIN sys.key_constraints pks
                   ON pks.[parent_object_id] = ind.[object_id]
                  AND pks.[unique_index_id] = ind.[index_id]
           WHERE  pks.[type] = 'PK'
          ) pkcol
       ON pkcol.[table_object_id] = tbl.[object_id]
      AND pkcol.[column_id] = col.[column_id]
LEFT JOIN 
    sys.foreign_key_columns fkc ON tbl.object_id = fkc.parent_object_id AND col.column_id = fkc.parent_column_id
LEFT JOIN 
    sys.foreign_keys fk ON fkc.constraint_object_id = fk.object_id 


WHERE 1=1
--  tbl.name does not start with __
AND tbl.name != '__RefactorLog'


ORDER BY tbl.[name], CONVERT(BIT, IIF(pkcol.index_id IS NOT NULL, 1, 0)) DESC, fk.name DESC, col.[name];


DECLARE @StuffedTable TABLE (TableName NVARCHAR(128), Columns NVARCHAR(MAX))
INSERT INTO @StuffedTable


SELECT 
    TableName,
STUFF
(
    (
        SELECT ',' + 
        CASE 
            WHEN PrimaryKey = 1 THEN '*' 
            WHEN ForeignKeyName IS NOT NULL THEN '+'            
            ELSE '' END +
        
        ColumnName + ' ' + [DataType] +
CASE 
WHEN [DataType] IN ('varchar','nvarchar') THEN ' (' + REPLACE(CONVERT(VARCHAR, [MaxLength]), '-1', 'max') + ')'
WHEN [DataType] = 'decimal' THEN ' (' + CONVERT(VARCHAR, [Precision]) + ',' + CONVERT(VARCHAR, [Scale]) + ')'
ELSE '' END


        FROM @FlatTable As T2
        WHERE T2.TableName = T1.TableName
        
        FOR XML PATH (''), TYPE
    ).value('.', 'nvarchar(max)')
, 1, 1, '') + ','


AS [Columns]
FROM @FlatTable As T1
GROUP BY TableName




DECLARE @ResultString NVARCHAR(MAX) = 
(SELECT 
STUFF
(
    (
        SELECT ',' + 
        '[' + TableName + ']' + ',' +
        Columns
        FROM @StuffedTable As T2        
        
        FOR XML PATH (''), TYPE
    ).value('.', 'nvarchar(max)')
, 1, 1, ''))


PRINT  
'```kroki' + CHAR(13) + CHAR(10) + 
'erd' + CHAR(13) + CHAR(10) + 
REPLACE(@ResultString, ',', CHAR(13) + CHAR(10))
+ '```' + CHAR(13) + CHAR(10)

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

DECLARE @FlatTable TABLE

(

TableName NVARCHAR(128),

ColumnName NVARCHAR(128),

DataType NVARCHAR(128),

[MaxLength] INT,

[Precision] INT,

Scale INT,

Nullable BIT,

[Identity] BIT,

PrimaryKey BIT,

ForeignKeyName NVARCHAR(128),

ParentTable NVARCHAR(128),

ParentColumn NVARCHAR(128),

ReferencedTable NVARCHAR(128),

ReferencedColumn NVARCHAR(128)

);

INSERT INTO @FlatTable

SELECT tbl.[name] AS [TableName],

col.[name] AS [ColumnName],

ty.[name] AS [DataType],

col.[max_length] AS [MaxLength],

col.[precision] AS [Precision],

col.[scale] AS [Scale],

col.[is_nullable] AS [Nullable],

col.[is_identity] AS [Identity],

CONVERT(BIT, IIF(pkcol.index_id IS NOT NULL, 1, 0)) AS [PrimaryKey],

fk.name AS ForeignKeyName,

OBJECT_NAME(fk.parent_object_id) AS ParentTable,

COL_NAME(fkc.parent_object_id, fkc.parent_column_id) AS ParentColumn,

OBJECT_NAME(fk.referenced_object_id) AS ReferencedTable,

COL_NAME(fkc.referenced_object_id, fkc.referenced_column_id) AS ReferencedColumn

FROM sys.tables tbl

INNER JOIN sys.columns col

ON col.[object_id] = tbl.[object_id]

INNER JOIN sys.types ty

ON ty.[user_type_id] = col.[user_type_id] -- do not use system_type_id

LEFT JOIN (

SELECT ind.[object_id] AS [table_object_id], ind.[index_id], ind.[column_id]

FROM sys.index_columns ind

INNER JOIN sys.key_constraints pks

ON pks.[parent_object_id] = ind.[object_id]

AND pks.[unique_index_id] = ind.[index_id]

WHERE pks.[type] = 'PK'

) pkcol

ON pkcol.[table_object_id] = tbl.[object_id]

AND pkcol.[column_id] = col.[column_id]

LEFT JOIN

sys.foreign_key_columns fkc ON tbl.object_id = fkc.parent_object_id AND col.column_id = fkc.parent_column_id

LEFT JOIN

sys.foreign_keys fk ON fkc.constraint_object_id = fk.object_id

WHERE 1=1

-- tbl.name does not start with __

AND tbl.name != '__RefactorLog'

ORDER BY tbl.[name], CONVERT(BIT, IIF(pkcol.index_id IS NOT NULL, 1, 0)) DESC, fk.name DESC, col.[name];

DECLARE @StuffedTable TABLE (TableName NVARCHAR(128), Columns NVARCHAR(MAX))

INSERT INTO @StuffedTable

SELECT

TableName,

STUFF

(

SELECT ',' +

CASE

WHEN PrimaryKey = 1 THEN '*'

WHEN ForeignKeyName IS NOT NULL THEN '+'

ELSE '' END +

ColumnName + ' ' + [DataType] +

CASE

WHEN [DataType] IN ('varchar','nvarchar') THEN ' (' + REPLACE(CONVERT(VARCHAR, [MaxLength]), '-1', 'max') + ')'

WHEN [DataType] = 'decimal' THEN ' (' + CONVERT(VARCHAR, [Precision]) + ',' + CONVERT(VARCHAR, [Scale]) + ')'

ELSE '' END

FROM @FlatTable As T2

WHERE T2.TableName = T1.TableName

FOR XML PATH (''), TYPE

).value('.', 'nvarchar(max)')

, 1, 1, '') + ','

AS [Columns]

FROM @FlatTable As T1

GROUP BY TableName

DECLARE @ResultString NVARCHAR(MAX) =

(SELECT

STUFF

(

SELECT ',' +

'[' + TableName + ']' + ',' +

Columns

FROM @StuffedTable As T2

FOR XML PATH (''), TYPE

).value('.', 'nvarchar(max)')

, 1, 1, ''))

'```kroki' + CHAR(13) + CHAR(10) +

'erd' + CHAR(13) + CHAR(10) +

REPLACE(@ResultString, ',', CHAR(13) + CHAR(10))

+ '```' + CHAR(13) + CHAR(10)

Posted on 18. März 2024 by Andreas in SQL

SQL Query um Tabellen und Spalten auszulesen inkl. PK und FK

SELECT tbl.[name] AS [TableName],
       col.[name] AS [ColumnName],
       ty.[name] AS [DataType],
       col.[max_length] AS [MaxLength],
       col.[precision] AS [Precision],
       col.[scale] AS [Scale],
       col.[is_nullable] AS [Nullable],
       col.[is_identity] AS [Identity],
       CONVERT(BIT, IIF(pkcol.index_id IS NOT NULL, 1, 0)) AS [PrimaryKey],
       fk.name AS ForeignKeyName,
       OBJECT_NAME(fk.parent_object_id) AS ParentTable,
       COL_NAME(fkc.parent_object_id, fkc.parent_column_id) AS ParentColumn,
       OBJECT_NAME(fk.referenced_object_id) AS ReferencedTable,
       COL_NAME(fkc.referenced_object_id, fkc.referenced_column_id) AS ReferencedColumn
FROM       sys.tables tbl
INNER JOIN sys.columns col
        ON col.[object_id] = tbl.[object_id]
INNER JOIN sys.types ty
        ON ty.[user_type_id] = col.[user_type_id] -- do not use system_type_id
LEFT JOIN (
           SELECT ind.[object_id] AS [table_object_id], ind.[index_id], ind.[column_id]
           FROM   sys.index_columns ind
           INNER JOIN sys.key_constraints pks
                   ON pks.[parent_object_id] = ind.[object_id]
                  AND pks.[unique_index_id] = ind.[index_id]
           WHERE  pks.[type] = 'PK'
          ) pkcol
       ON pkcol.[table_object_id] = tbl.[object_id]
      AND pkcol.[column_id] = col.[column_id]
LEFT JOIN
    sys.foreign_key_columns fkc ON tbl.object_id = fkc.parent_object_id AND col.column_id = fkc.parent_column_id
LEFT JOIN
    sys.foreign_keys fk ON fkc.constraint_object_id = fk.object_id      
ORDER BY tbl.[name], col.[name];

SELECT tbl.[name] AS [TableName],

col.[name] AS [ColumnName],

ty.[name] AS [DataType],

col.[max_length] AS [MaxLength],

col.[precision] AS [Precision],

col.[scale] AS [Scale],

col.[is_nullable] AS [Nullable],

col.[is_identity] AS [Identity],

CONVERT(BIT, IIF(pkcol.index_id IS NOT NULL, 1, 0)) AS [PrimaryKey],

fk.name AS ForeignKeyName,

OBJECT_NAME(fk.parent_object_id) AS ParentTable,

COL_NAME(fkc.parent_object_id, fkc.parent_column_id) AS ParentColumn,

OBJECT_NAME(fk.referenced_object_id) AS ReferencedTable,

COL_NAME(fkc.referenced_object_id, fkc.referenced_column_id) AS ReferencedColumn

FROM sys.tables tbl

INNER JOIN sys.columns col

ON col.[object_id] = tbl.[object_id]

INNER JOIN sys.types ty

ON ty.[user_type_id] = col.[user_type_id] -- do not use system_type_id

LEFT JOIN (

SELECT ind.[object_id] AS [table_object_id], ind.[index_id], ind.[column_id]

FROM sys.index_columns ind

INNER JOIN sys.key_constraints pks

ON pks.[parent_object_id] = ind.[object_id]

AND pks.[unique_index_id] = ind.[index_id]

WHERE pks.[type] = 'PK'

) pkcol

ON pkcol.[table_object_id] = tbl.[object_id]

AND pkcol.[column_id] = col.[column_id]

LEFT JOIN

sys.foreign_key_columns fkc ON tbl.object_id = fkc.parent_object_id AND col.column_id = fkc.parent_column_id

LEFT JOIN

sys.foreign_keys fk ON fkc.constraint_object_id = fk.object_id

ORDER BY tbl.[name], col.[name];

Posted on 18. März 2024 by Andreas in SQL

SQL Server Statistik IO und Time

SET STATISTICS IO ON
SET STATISTICS TIME ON
SELECT er.ReportID, er.ReportName, er.ReportNumber
FROM dbo.EmployeeReports er
WHERE er.ReportNumber LIKE '%33%'
SET STATISTICS IO OFF
SET STATISTICS TIME OFF

SET STATISTICS IO ON

SET STATISTICS TIME ON

SELECT er.ReportID, er.ReportName, er.ReportNumber

FROM dbo.EmployeeReports er

WHERE er.ReportNumber LIKE '%33%'

SET STATISTICS IO OFF

SET STATISTICS TIME OFF

Posted on 14. Januar 2024 by Andreas in SQL

system-versioned temporal tables oder automatische Historie auf Tabellenbasis

funktioniert ab SQL Server 2016

/*
       System Versionisierung wieder deaktivieren
*/

--ALTER TABLE dbo.MyTable SET (SYSTEM_VERSIONING = OFF)
--ALTER TABLE dbo.MyTable DROP PERIOD FOR SYSTEM_TIME;
--GO
--ALTER TABLE [dbo].[MyTable] DROP CONSTRAINT [DF_MyTable_ValidFrom];
--GO
--ALTER TABLE [dbo].[MyTable] DROP COLUMN [ValidFrom];
--GO
--ALTER TABLE [dbo].[MyTable] DROP CONSTRAINT [DF_MyTable_ValidTo];
--GO
--ALTER TABLE [dbo].[MyTable] DROP COLUMN [ValidTo];

/*
       System Versionisierung aktivieren
*/

-- Neue Spalten hinzugügen: ValidFrom, ValidTo
ALTER TABLE dbo.MyTable ADD 
  [ValidFrom] datetime2(7) GENERATED ALWAYS AS ROW START HIDDEN CONSTRAINT DF_MyTable_ValidFrom DEFAULT DATEADD(SECOND,-1, SYSUTCDATETIME()),
  ValidTo datetime2(7) GENERATED ALWAYS AS ROW END HIDDEN CONSTRAINT DF_MyTable_ValidTo DEFAULT CONVERT(datetime2 (7), '9999-12-31 23:59:59.9999999'),
  PERIOD FOR SYSTEM_TIME ([ValidFrom], ValidTo);
GO

-- History Tabelle erzeugen
ALTER TABLE dbo.MyTable
SET(SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.MyTable_History, DATA_CONSISTENCY_CHECK = ON))
GO

System Versionisierung wieder deaktivieren

--ALTER TABLE dbo.MyTable SET (SYSTEM_VERSIONING = OFF)

--ALTER TABLE dbo.MyTable DROP PERIOD FOR SYSTEM_TIME;

--GO

--ALTER TABLE [dbo].[MyTable] DROP CONSTRAINT [DF_MyTable_ValidFrom];

--GO

--ALTER TABLE [dbo].[MyTable] DROP COLUMN [ValidFrom];

--GO

--ALTER TABLE [dbo].[MyTable] DROP CONSTRAINT [DF_MyTable_ValidTo];

--GO

--ALTER TABLE [dbo].[MyTable] DROP COLUMN [ValidTo];

System Versionisierung aktivieren

-- Neue Spalten hinzugügen: ValidFrom, ValidTo

ALTER TABLE dbo.MyTable ADD

[ValidFrom] datetime2(7) GENERATED ALWAYS AS ROW START HIDDEN CONSTRAINT DF_MyTable_ValidFrom DEFAULT DATEADD(SECOND,-1, SYSUTCDATETIME()),

ValidTo datetime2(7) GENERATED ALWAYS AS ROW END HIDDEN CONSTRAINT DF_MyTable_ValidTo DEFAULT CONVERT(datetime2 (7), '9999-12-31 23:59:59.9999999'),

PERIOD FOR SYSTEM_TIME ([ValidFrom], ValidTo);

-- History Tabelle erzeugen

ALTER TABLE dbo.MyTable

SET(SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.MyTable_History, DATA_CONSISTENCY_CHECK = ON))

Posted on 15. März 2021 by Andreas in SQL

SQL Column löschen inkl. aller Constraints

DECLARE @TableSchema NVARCHAR(255) = '';
DECLARE @TableName NVARCHAR(255) = '';
DECLARE @ColumnName NVARCHAR(255) = '';

DECLARE @sql NVARCHAR(MAX)
WHILE 1=1
BEGIN
    SELECT TOP 1 @sql = N'ALTER TABLE ' + @TableSchema + '.' + @TableName + ' DROP CONSTRAINT ['+dc.NAME+N']'
    FROM sys.default_constraints dc
    LEFT JOIN sys.columns c ON c.default_object_id = dc.object_id
    WHERE 		
        dc.parent_object_id = OBJECT_ID(@TableSchema + '.' + @TableName)
    AND c.name = @ColumnName
    IF @@ROWCOUNT = 0 BREAK
    EXEC (@sql)
END

IF EXISTS (SELECT 1
               FROM   INFORMATION_SCHEMA.COLUMNS
               WHERE  TABLE_SCHEMA=@TableSchema
            AND TABLE_NAME = @TableName
                      AND COLUMN_NAME = @ColumnName
                      )
  BEGIN
      EXEC ('ALTER TABLE ' + @TableSchema + '.' + @TableName + ' DROP COLUMN ' + @ColumnName)
  END
GO

DECLARE @TableSchema NVARCHAR(255) = '';

DECLARE @TableName NVARCHAR(255) = '';

DECLARE @ColumnName NVARCHAR(255) = '';

DECLARE @sql NVARCHAR(MAX)

WHILE 1=1

BEGIN

SELECT TOP 1 @sql = N'ALTER TABLE ' + @TableSchema + '.' + @TableName + ' DROP CONSTRAINT ['+dc.NAME+N']'

FROM sys.default_constraints dc

LEFT JOIN sys.columns c ON c.default_object_id = dc.object_id

WHERE

dc.parent_object_id = OBJECT_ID(@TableSchema + '.' + @TableName)

AND c.name = @ColumnName

IF @@ROWCOUNT = 0 BREAK

EXEC (@sql)

END

IF EXISTS (SELECT 1

FROM INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_SCHEMA=@TableSchema

AND TABLE_NAME = @TableName

AND COLUMN_NAME = @ColumnName

)

BEGIN

EXEC ('ALTER TABLE ' + @TableSchema + '.' + @TableName + ' DROP COLUMN ' + @ColumnName)

END

Posted on 22. Januar 2021 by Andreas in SQL

Backup alle Nicht System Datenbanken

--Script 3: Backup all non-system databases

--1. Variable declaration

DECLARE @path VARCHAR(500)
DECLARE @name VARCHAR(500)
DECLARE @filename VARCHAR(256)
DECLARE @time DATETIME
DECLARE @year VARCHAR(4)
DECLARE @month VARCHAR(2)
DECLARE @day VARCHAR(2)
DECLARE @hour VARCHAR(2)
DECLARE @minute VARCHAR(2)
DECLARE @second VARCHAR(2)
 
-- 2. Setting the backup path

SET @path = 'C:\Temp\OneDrive\DevAndy\Backups Kingside\Database Backup\'  

 -- 3. Getting the time values

SELECT @time = GETDATE()
SELECT @year   = (SELECT CONVERT(VARCHAR(4), DATEPART(yy, @time)))
SELECT @month  = (SELECT CONVERT(VARCHAR(2), FORMAT(DATEPART(mm,@time),'00')))
SELECT @day    = (SELECT CONVERT(VARCHAR(2), FORMAT(DATEPART(dd,@time),'00')))
SELECT @hour   = (SELECT CONVERT(VARCHAR(2), FORMAT(DATEPART(hh,@time),'00')))
SELECT @minute = (SELECT CONVERT(VARCHAR(2), FORMAT(DATEPART(mi,@time),'00')))
SELECT @second = (SELECT CONVERT(VARCHAR(2), FORMAT(DATEPART(ss,@time),'00')))

-- 4. Defining cursor operations
 
DECLARE db_cursor CURSOR FOR  
SELECT name 
FROM master.dbo.sysdatabases 
WHERE name NOT IN ('master','model','msdb','tempdb')  -- system databases are excluded

--5. Initializing cursor operations

OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @name   

WHILE @@FETCH_STATUS = 0   
BEGIN

-- 6. Defining the filename format   
       SET @fileName = CONCAT(@path, @name, '_backup_', @year, '_', @month, '_', @day, '_', @hour, '_', @minute, '_', @second, '.bak')  
       BACKUP DATABASE @name TO DISK = @fileName  

 
       FETCH NEXT FROM db_cursor INTO @name   
END   
CLOSE db_cursor   
DEALLOCATE db_cursor

--Script 3: Backup all non-system databases

--1. Variable declaration

DECLARE @path VARCHAR(500)

DECLARE @name VARCHAR(500)

DECLARE @filename VARCHAR(256)

DECLARE @time DATETIME

DECLARE @year VARCHAR(4)

DECLARE @month VARCHAR(2)

DECLARE @day VARCHAR(2)

DECLARE @hour VARCHAR(2)

DECLARE @minute VARCHAR(2)

DECLARE @second VARCHAR(2)

-- 2. Setting the backup path

SET @path = 'C:\Temp\OneDrive\DevAndy\Backups Kingside\Database Backup\'

-- 3. Getting the time values

SELECT @time = GETDATE()

SELECT @year = (SELECT CONVERT(VARCHAR(4), DATEPART(yy, @time)))

SELECT @month = (SELECT CONVERT(VARCHAR(2), FORMAT(DATEPART(mm,@time),'00')))

SELECT @day = (SELECT CONVERT(VARCHAR(2), FORMAT(DATEPART(dd,@time),'00')))

SELECT @hour = (SELECT CONVERT(VARCHAR(2), FORMAT(DATEPART(hh,@time),'00')))

SELECT @minute = (SELECT CONVERT(VARCHAR(2), FORMAT(DATEPART(mi,@time),'00')))

SELECT @second = (SELECT CONVERT(VARCHAR(2), FORMAT(DATEPART(ss,@time),'00')))

-- 4. Defining cursor operations

DECLARE db_cursor CURSOR FOR

SELECT name

FROM master.dbo.sysdatabases

WHERE name NOT IN ('master','model','msdb','tempdb') -- system databases are excluded

--5. Initializing cursor operations

OPEN db_cursor

FETCH NEXT FROM db_cursor INTO @name

WHILE @@FETCH_STATUS = 0

BEGIN

-- 6. Defining the filename format

SET @fileName = CONCAT(@path, @name, '_backup_', @year, '_', @month, '_', @day, '_', @hour, '_', @minute, '_', @second, '.bak')

BACKUP DATABASE @name TO DISK = @fileName

FETCH NEXT FROM db_cursor INTO @name

END

CLOSE db_cursor

DEALLOCATE db_cursor

Quelle: https://www.sqlshack.com/multiple-methods-for-scheduling-a-sql-server-backup-automatically/

Posted on 8. August 2020 by Andreas in SQL

SQL CPU Last und Gesamtdauer von Query testen

set statistics time on
 
select 1


set statistics time off

set statistics time on

select 1

set statistics time off

Posted on 26. Mai 2020 by Andreas in SQL

SQL Rekursion Parent zu Child / Child zu Parent

Erster Block im CTE: In der WHERE Bedingung wird die Child-Id übergeben Zweiter Block: Join auf die CTE, ParentId mit der TabelleId

DECLARE @IterationId INT = 4;


-- Rekursion Child to Parent
;WITH CTE AS (
   SELECT t1.IterationId
		  , t1.ParentId	  		  

		  , 0 AS [Level]	
    FROM Messenger.tblIteration t1 	
	WHERE t1.IterationId = @IterationId	

   UNION ALL
   SELECT 
			t1.IterationId
		  , t1.ParentId	  		  		  
		  
		  , CTE.[Level] + 1 AS [Level]	

	FROM Messenger.tblIteration t1 
	INNER JOIN CTE ON CTE.ParentId = t1.IterationId 
)

SELECT 
*
FROM CTE
ORDER BY Level

DECLARE @IterationId INT = 4;

-- Rekursion Child to Parent

;WITH CTE AS (

SELECT t1.IterationId

, t1.ParentId

, 0 AS [Level]

FROM Messenger.tblIteration t1

WHERE t1.IterationId = @IterationId

UNION ALL

SELECT

t1.IterationId

, t1.ParentId

, CTE.[Level] + 1 AS [Level]

FROM Messenger.tblIteration t1

INNER JOIN CTE ON CTE.ParentId = t1.IterationId

)

SELECT

FROM CTE

ORDER BY Level

Erster Block im CTE: In der WHERE Bedingung wird die ParentId mit NULL angegeben Zweiter Block: Join auf die CTE, Id mit der Tabelle ParentId

---- Rekursion Parent to Child
;WITH CTE AS (
   SELECT t1.IterationId
		  , t1.ParentId	  		  

		  , 0 AS [Level]	
    FROM Messenger.tblIteration t1 	
	WHERE t1.ParentId IS NULL

   UNION ALL
   SELECT 
			t1.IterationId
		  , t1.ParentId	  		  		  
		  
		  , CTE.[Level] + 1 AS [Level]	

	FROM Messenger.tblIteration t1 
	INNER JOIN CTE ON CTE.IterationId = t1.ParentId 
)

SELECT 
*
FROM CTE
ORDER BY Level

---- Rekursion Parent to Child

;WITH CTE AS (

SELECT t1.IterationId

, t1.ParentId

, 0 AS [Level]

FROM Messenger.tblIteration t1

WHERE t1.ParentId IS NULL

UNION ALL

SELECT

t1.IterationId

, t1.ParentId

, CTE.[Level] + 1 AS [Level]

FROM Messenger.tblIteration t1

INNER JOIN CTE ON CTE.IterationId = t1.ParentId

)

SELECT

FROM CTE

ORDER BY Level

Posted on 6. Januar 2020 by Andreas in SQL

SQL Server Remote Zugriff

Um auf eine SQL Instanz per Remote (von außen) zugreifen zu können, muss man in der Firewall folgende Ports aufmachen: InBound (Eingehende): TCP 1433 (für z.B. SSMS), UDP 1434 (für ODBC Verbindungen) OutBound (Ausgehende): TCP 1433 Zusätzlich die Dynamic aus “SQL Server Network Configuration” -> “Protocols for [INSTANZ]” -> “TCP/IP” -> Reiter “IPAdresses” -> Im […]

Posted on 1. Dezember 2019 by Andreas in Allgemein, SQL

Tags: Remote, SQL, von außen

Query für Rekursives löschen

Posted on 29. Januar 2019 by Andreas in SQL

Tags: Rekursiv

Denn ich habe kein Gefallen am Tod dessen, der sterben muss, spricht Gott, der Herr. So kehrt denn um, und ihr sollt leben!

Category Archives: SQL

SQL Server Datenbank Design -> Kroki ER Diagramm

SQL Query um Tabellen und Spalten auszulesen inkl. PK und FK

SQL Server Statistik IO und Time

system-versioned temporal tables oder automatische Historie auf Tabellenbasis

SQL Column löschen inkl. aller Constraints

Backup alle Nicht System Datenbanken

SQL CPU Last und Gesamtdauer von Query testen

SQL Rekursion Parent zu Child / Child zu Parent

SQL Server Remote Zugriff

Query für Rekursives löschen

Seiten

Neueste Beiträge

Neueste Kommentare

Archive

Denn ich habe kein Gefallen am Tod dessen, der sterben muss, spricht Gott, der Herr. So kehrt denn um, und ihr sollt leben!

Category Archives: SQL

Search this site

Seiten

Neueste Beiträge

Neueste Kommentare

Archive