In diesem Blogbeitrag werden die pragmatischen Aspekte einer datenbankübergreifenden Dokumentation von SQL-Objekten stichpunktartig erläutert und einige ihrer Facetten näher betrachtet.

Eine solide Datenprojekt-Dokumentation mit den vielfältigen Verknüpfungen innerhalb von Datenbanken kann recht umfangreich werden und daher stellt sich die Frage, welche einfachen Hilfsmittel hierfür generell angebracht sind, die jeweiligen Strukturen und deren Beziehungen darzulegen.

Wer sich sinnvollerweise die Mühe macht und seine erstellten SQL-Objekte und vor allem deren Verknüpfungen grundlegend dokumentiert, hat bei späteren Anpassungen ein leichteres Vorgehen.
Die folgenden SQL-Skripte können bei der grundlegenden generischen Dokumentation von Datenbanken helfen. Der hier beschriebene Aufwand hält sich in Grenzen und lohnt sich auf alle Fälle.

Allgemeine Situation

Jeder, der SQL-Skripte schreibt, weiß, dass eine Dokumentation sehr wesentlich ist: Zu jeder Programmierung gehört auch die Dokumentation als verständliche Beschreibung des Datenmodells.
Aber nicht immer wird eine Dokumentation formuliert, entweder weil die notwendige Zeit einfach fehlt oder die disziplinierte Beschreibung des Quellcodes zu lästig ist.
In der Regel basieren Datenmodelle auf vorgelagerten Datenbanken, welche als verschiedene Daten-quellen zur Verfügung stehen. In diesen laufen vielfältige Skripte, welche bestimmte Tabellen bearbeiten und aufbereiten, so dass die Datenbanken voneinander abhängig werden.
Solange noch aktiv an Skripten im Projekt gearbeitet wird, besteht meistens kein Problem zu verstehen, wie der Code und die Zusammenhänge funktionieren.
Anders sieht es aus wenn mehrere Personen an einem Projekt arbeiten, ein größerer zeitlicher Abstand zur letzten Änderung besteht oder eine Weiterentwicklung eines möglicherweise zusätzlichen Projekts ansteht.
Somit sind in den meisten Fällen die erstellten Skripte letztlich nicht mehr im Ursprungszustand, weil sich die Anforderungen im Nachhinein schrittweise geändert haben.
Manchmal ist es sogar der Fall, dass solange herumgebastelt wird, bis alle Wünsche irgendwie erfüllt werden konnten und ohne eine gute Dokumentation blickt dann leider niemand mehr so richtig durch.
Im Allgemeinen sollte es Ansätze geben, die dem Verfasser von SQL-Skripten das Leben leichter, effektiver und effizienter macht.
In diesem Blogbeitrag geht es darum, wie die Erzeugung und Aktualisierung von gängigen Grundlagen automatisiert und vereinfacht werden kann. Es werden einige interessante Möglichkeiten aufgezeigt.
Der hier beschriebene Aufwand hält sich in Grenzen und lohnt sich auf alle Fälle.

Per SQL eine kleine Datenbankdokumentation erstellen

Die folgenden SQL-Skripte können bei der grundlegenden generischen Dokumentation von Datenbanken helfen.

Die Objekte einer Datenbank

Zu Beginn ein kleines SQL-Skript, welches alle Objekte einer Datenbank aktuell ausgibt:

SELECT 	'Datenbank'       			AS [DataBase]
 	, 	sys.schemas.name        		AS SchemaName
 	,   	sys.objects.name           	AS ObjectName
 	,	sys.objects.type        		AS ObjectType
 	,	sys.objects.create_date 		AS DateCreated
 	, 	sys.objects.modify_date 	AS DateModified
FROM   	sys.objects 
INNER JOIN sys.schemas
	ON	sys.schemas.schema_id = sys.objects.schema_id
ORDER BY 	sys.schemas.name
 	, 	sys.objects.type, sys.objects.name

Die Felder der Tabellen und Sichten einer Datenbank

Hier ein kleines SQL-Skript, welches alle Tabellen und Sichten einer Datenbank mit Spalten und Datentypen aktuell ausgibt:

SELECT 	'Datenbank'       			AS [DataBase]
 	, 	sys.schemas.name    		AS SchemaName
 	,	sys.objects.type    		AS ObjectType
 	,	sys.objects.name     		AS ObjectName
 	,	sys.columns.name     		AS ColumnName
 	, 	sys.types.name       		AS ColumnType
 	,   	sys.columns.max_length	AS ColumnLength
FROM   	sys.objects
INNER JOIN sys.columns
 	ON 	sys.objects.[object_id] = sys.columns.[object_id]
INNER JOIN sys.schemas
 	ON 	sys.schemas.schema_id = sys.objects.schema_id
INNER JOIN sys.types
	ON 	sys.columns.system_type_id = sys.types.system_type_id
WHERE  	sys.objects.type IN ('U', 'V')
			-- U=USER_TABLE, S=SYSTEM_TABLE
			-- V=VIEW, P=SQL_STORED_PROCEDURE
	AND 	sys.types.name <> 'sysname'
ORDER BY 	sys.schemas.name
 	,	sys.objects.type, sys.objects.name
 	, 	sys.columns.column_id

Die Anzahl der Tabellenzeilen schnell und einfach ausgeben

Die Fragestellung „Wie viele Zeilen haben die Tabellen?“ gibt es immer. Die Standardantwort als klassi-scher Ansatz lautet dann:

SELECT 
		COUNT(*)
FROM 
 		[dbo].[Tabelle]

Allerdings können hierbei zwei Probleme auftreten: Der SQL-Server muss zum Bearbeiten dieser Abfrage einen Table-Scan durchführen. Das kann bei Tabellen mit mehreren Millionen Datensätzen eine Weile dauern. Und man kann mit dieser Abfrage nur jeweils eine Tabelle abfragen.
Was macht man aber, wenn man die Zeilenanzahl aller Tabellen einer Datenbank gleichzeitig bestimmen möchte? Mit Hilfe der folgenden Abfrage kann diese Fragestellung problemlos und aktuell gelöst werden:

SELECT 
 		'Datenbank'       					AS [DataBase]
 	, 	sys.schemas.name        				AS SchemaName
 	, 	sys.objects.name 					AS ObjectName
 	, 	sys.dm_db_partition_stats.row_count  	AS [RowCount]
FROM 
 		sys.indexes 
INNER JOIN 
 		sys.objects 
	ON 	sys.indexes.OBJECT_ID = sys.objects.OBJECT_ID
INNER JOIN 
		sys.schemas
	ON	sys.schemas.schema_id = sys.objects.schema_id
INNER JOIN 
		sys.dm_db_partition_stats 
	ON 	sys.indexes.OBJECT_ID = sys.dm_db_partition_stats.OBJECT_ID
	AND 	sys.indexes.index_id = sys.dm_db_partition_stats.index_id
WHERE 
		sys.indexes.index_id < 2
	AND 	sys.objects.is_ms_shipped = 0
ORDER BY 
		sys.schemas.name
	,  	sys.objects.name

Dieses kurze SQL-Skript zeigt alle Benutzertabellen mit der zugehörigen aktuellen Zeilenanzahl.

Der „index_id < 2“ filtert nach „clustered index“ (1) und „hash table“ (0).

Damit auch die Systemtabellen angezeigt werden, muss der Filter “is_ms_shipped = 0” entfernt werden.

Die Abhängigkeiten der Datenbankobjekte

Hier ein kleines SQL-Skript, welches die Objekte und deren Abhängigkeiten einer Datenbank aktuell ausgibt. Hierbei werden auch materialisierte Sichten zusätzlich auf ihre originären Sichten verlinkt:

SELECT	--   Objekte ohne Abhängigkeiten
		''         		AS referencing_server
	,	'Datenbank'       	AS referencing_database		--Datenbankname
 	,  	'dbo'              	AS referencing_schema 		--Datenbankschema
 	, 	O.name             	AS referencing_name
	,  	O.type             	AS referencing_type
	, 	O.object_id        	AS referencing_id
	,	''       	      		AS referenced_server
	, 	''   	        		AS referenced_database
	,  	''              		AS referenced_schema
	,   	''            		AS referenced_name
	, 	NULL     		AS referenced_id
FROM 
		sys.objects AS O
UNION ALL
SELECT	--   Materialisierte Sichten und deren originäre Sichten
		'' AS referencing_server
	,	'Datenbank' AS referencing_database		--Datenbankname
	,  	'dbo' AS referencing_schema				--Datenbankschema
	,	O.name AS referencing_name
	,	O.type AS referencing_type
	,	O.object_id AS referencing_id
	,  	'' AS referenced_server
	,	'Datenbank' AS referenced_database		--Datenbankname
 	,	'dbo' AS referenced_schema 				--Datenbankschema
	, 	REPLACE(O.name, 'TMV_', 'V_') AS referenced_name
 	,  	NULL AS referenced_id
FROM
		sys.objects AS O

WHERE 
		LEFT(O.name,4) = 'TMV_'   
UNION ALL 
SELECT 	--   Objektabhängigkeiten
 		'' AS referencing_server
 	,	'Datenbank' AS referencing_database 		--Datenbankname
	, 	'dbo' AS referencing_schema     			--Datenbankschema
 	,	OBJECT_NAME(D.referencing_id) AS referencing_name
 	,  	ISNULL(O.type,'') AS referencing_type
 	,	D.referencing_id 
 	,    	ISNULL(D.referenced_server_name,'') AS referenced_server
 	,   	ISNULL(D.referenced_database_name,'Datenbank') AS referenced_database

 	,  	ISNULL(D.referenced_schema_name,'dbo') AS referenced_schema
 	,  	ISNULL(D.referenced_entity_name,'') AS referenced_name    
 	, 	D.referenced_id
FROM 
 		sys.sql_expression_dependencies AS D
LEFT OUTER JOIN
		sys.objects AS O
 	ON	OBJECT_NAME(D.referencing_id) = O.name

Ein solches SQL-Skript sollte in jeder Datenbank angelegt sein, welche man in diesem Zusammenhang mitbetrachten möchte.
Diese vorgelagerten SQL-Skripte können dann an dieser Stelle per „union all“ ebenfalls hinzugezogen werden, um eine datenbankübergreifende Dokumentation zu erstellen.

Die Reihenfolge der Objektabhängigkeiten (Ziel zu allen Quellen)

Hier ein kleines SQL-Skript, welches die zusammenhängende Reihenfolge der Objektabhängigkeiten vom Ziel zu allen Quellen basierend der SQL-Sicht von 3.4 aktuell ausgibt:

SELECT  
 		O.referencing_server    				AS [Server]
 	, 	O.referencing_database  			AS [Database]
 	, 	O.referencing_schema    			AS [Schema]
 	,  	O.referencing_type           			AS ObjectType
 	, 	O.referencing_object    				AS ObjectName
 	,  	ISNULL(O.referenced_object5,
 		ISNULL(O.referenced_object4,
 		ISNULL(O.referenced_object3, 
 		ISNULL(O.referenced_object2,
 		ISNULL(O.referenced_object1,
 		''))))) 							AS ReferenceName
 	,	ISNULL(O.referenced_object1, '')   		AS ReferenceObject1
 	,	ISNULL(O.referenced_object2, '')   		AS ReferenceObject2
 	,	ISNULL(O.referenced_object3, '')   		AS ReferenceObject3
 	,	ISNULL(O.referenced_object4, '')   		AS ReferenceObject4
 	,	ISNULL(O.referenced_object5, '')   		AS ReferenceObject5
FROM	(
 		SELECT 
				A.referencing_server
			,	A.referencing_database
 			,   	A.referencing_schema
 			,	A.referencing_name
 			, 	A.referencing_type 
 			, 	A.referencing_id 
 			, 	A.referenced_server 
 			, 	A.referenced_database 
 			,	A.referenced_schema 
 			, 	A.referenced_name 
 			, 	A.referenced_id
 			, 	CASE 
 				WHEN 	A.referencing_name <> '' 
 					OR 	A.referencing_name IS NULL
 					THEN  	A.referencing_database+'.'+ 
 							A.referencing_schema+'.'+  
 							A.referencing_name
 				ELSE  NULL
 				END     		AS referencing_object
 			,	CASE
 				WHEN 	A.referenced_name <> ''
 					OR 	A.referenced_name IS NULL
 					THEN  	A.referenced_database+'.'+ 
 							A.referenced_schema+'.'+  
 							A.referenced_name
 				ELSE  NULL
 				END         	AS referenced_object1
 			,	CASE
 				WHEN 	B.referenced_name <> ''
 					OR 	B.referenced_name IS NULL
 					THEN  	B.referenced_database+'.'+ 
 							B.referenced_schema+'.'+  
 							B.referenced_name
 				ELSE  NULL
 				END         	AS referenced_object2
 			,	CASE
 				WHEN 	C.referenced_name <> ''
 					OR 	C.referenced_name IS NULL
 					THEN  	C.referenced_database+'.'+ 
 							C.referenced_schema+'.'+  
 							C.referenced_name
 				ELSE  NULL
 				END         	AS referenced_object3
 			,	CASE
 				WHEN 	D.referenced_name <> ''
 					OR 	D.referenced_name IS NULL
 					THEN  	D.referenced_database+'.'+ 
 							D.referenced_schema+'.'+  
 							D.referenced_name
 				ELSE  NULL
 				END         	AS referenced_object4
 			,	CASE
 				WHEN 	E.referenced_name <> ''
 					OR 	E.referenced_name IS NULL
 					THEN  	E.referenced_database+'.'+ 
 							E.referenced_schema+'.'+  
 							E.referenced_name
 				ELSE  NULL
 				END         	AS referenced_object5
     	FROM 
 				[SQL-Sicht aus 3.4] 	AS A
 		LEFT OUTER JOIN
 				[SQL-Sicht aus 3.4] 	AS B
 			ON  	A.referenced_database 	= B.referencing_database
 			AND 	A.referenced_schema 	= B.referencing_schema
 			AND 	A.referenced_name	= B.referencing_name 
 		--  	AND 	A.referenced_server    	= B.referencing_server 
 		--	AND 	A.referenced_id  		= B.referencing_id
		LEFT OUTER JOIN
 				[SQL-Sicht aus 3.4] 	AS C
 			ON  	B.referenced_database 	= C.referencing_database
 			AND 	B.referenced_schema 	= C.referencing_schema
 			AND 	B.referenced_name	= C.referencing_name 
 		--  	AND 	B.referenced_server    	= C.referencing_server 
 		--	AND 	B.referenced_id  		= C.referencing_id
 		LEFT OUTER JOIN
 				[SQL-Sicht aus 3.4] 	AS D
 			ON  	C.referenced_database 	= D.referencing_database
 			AND 	C.referenced_schema 	= D.referencing_schema
 			AND 	C.referenced_name	= D.referencing_name 
 		--  	AND 	C.referenced_server    	= D.referencing_server 
 		--	AND 	C.referenced_id  		= D.referencing_id
 		LEFT OUTER JOIN
 				[SQL-Sicht aus 3.4] 	AS E
 			ON  	D.referenced_database = E.referencing_database
 			AND 	D.referenced_schema 	= E.referencing_schema
 			AND 	D.referenced_name	= E.referencing_name 
 		--  	AND 	D.referenced_server    	= E.referencing_server 
 		--	AND 	D.referenced_id  		= E.referencing_id
 	) 	AS O
ORDER BY 	
 		O.referencing_server
 	, 	O.referencing_database
 	, 	O.referencing_schema 
 	,   	O.referencing_object
 	, 	O.referenced_object1 
 	, 	O.referenced_object2
 	, 	O.referenced_object3 
 	, 	O.referenced_object4
 	, 	O.referenced_object5

Die Reihenfolge der Objektabhängigkeiten (Quelle zu allen Zielen)

Hier ein kleines SQL-Skript, welches die zusammenhängende Reihenfolge der Objektabhängigkeiten von der Quelle zu allen Zielen basierend der SQL-Sicht von 3.4 aktuell ausgibt:

SELECT  
 		O.referenced_server    				AS [Server]
 	, 	O.referenced_database  			AS [Database]
 	, 	O.referenced_schema    			AS [Schema]
 	,  	O.referenced_type           			AS ObjectType
 	, 	O.referenced_object    				AS ObjectName
 	,  	ISNULL(O.referencing_object5,
 		ISNULL(O.referencing_object4,
 		ISNULL(O.referencing_object3, 
 		ISNULL(O.referencing_object2,
 		ISNULL(O.referencing_object1,
 		''))))) 							AS ReferenceName
 	,	ISNULL(O.referencing_object1, '')   		AS ReferenceObject1
 	,	ISNULL(O.referencing_object2, '')   		AS ReferenceObject2
 	,	ISNULL(O.referencing_object3, '')   		AS ReferenceObject3
 	,	ISNULL(O.referencing_object4, '')   		AS ReferenceObject4
 	,	ISNULL(O.referencing_object5, '')   		AS ReferenceObject5
FROM	(
 		SELECT 
 				A.referenced_server
			,	A.referenced_database
 			,   	A.referenced_schema
 			,	A.referenced_name
 			, 	A.referenced_type 
 			, 	A.referenced_id 
 			, 	A.referencing_server 
 			, 	A.referencing_database 
 			,	A.referencing_schema 
 			, 	A.referencing_name 
 			, 	A.referencing_id
 			, 	CASE 
 				WHEN 	A.referenced_name <> '' 
 					OR 	A.referenced_name IS NULL
 					THEN  	A.referenced_database+'.'+ 
 							A.referenced_schema+'.'+  
 							A.referenced_name
 				ELSE  NULL
 				END     		AS referenced_object
 			,	CASE
 				WHEN 	A.referencing_name <> ''
 					OR 	A.referencing_name IS NULL
 					THEN  	A.referencing_database+'.'+ 
 							A.referencing_schema+'.'+  
 							A.referencing_name
 				ELSE  NULL
 				END         	AS referencing_object1
 			,	CASE
 				WHEN 	B.referencing_name <> ''
 					OR 	B.referencing_name IS NULL
 					THEN  	B.referencing_database+'.'+ 
 							B.referencing_schema+'.'+  
 							B.referencing_name
 				ELSE  NULL
 				END         	AS referencing_object2
 			,	CASE
 				WHEN 	C.referencing_name <> ''
 					OR 	C.referencing_name IS NULL
 					THEN  	C.referencing_database+'.'+ 
 							C.referencing_schema+'.'+  
 							C.referencing_name
 				ELSE  NULL
 				END         	AS referencing_object3
 			,	CASE
 				WHEN 	D.referencing_name <> ''
 					OR 	D.referencing_name IS NULL
 					THEN  	D.referencing_database+'.'+ 
 							D.referencing_schema+'.'+  
 							D.referencing_name
 				ELSE  NULL
 				END         	AS referencing_object4
 			,	CASE
 				WHEN 	E.referencing_name <> ''
 					OR 	E.referencing_name IS NULL
 					THEN  	E.referencing_database+'.'+ 
 							E.referencing_schema+'.'+  
 							E.referencing_name
 				ELSE  NULL
 				END         	AS referencing_object5
     	FROM 
 				[SQL-Sicht aus 3.4] 	AS A
 		LEFT OUTER JOIN
 				[SQL-Sicht aus 3.4] 	AS B
 			ON  	A.referencing_database = B.referenced_database
 			AND 	A.referencing_schema 	= B.referenced_schema
 			AND 	A.referencing_name	= B.referenced_name 
 		--  	AND 	A.referencing_server   	= B.referenced_server 
 		--	AND 	A.referencing_id  		= B.referenced_id
		LEFT OUTER JOIN
 				[SQL-Sicht aus 3.4] 	AS C
 			ON  	B.referencing_database = C.referenced_database
 			AND 	B.referencing_schema 	= C.referenced_schema
 			AND 	B.referencing_name	= C.referenced_name 
 		--  	AND 	B.referencing_server   	= C.referenced_server 
 		--	AND 	B.referencing_id  		= C.referenced_id
 		LEFT OUTER JOIN
 				[SQL-Sicht aus 3.4] 	AS D
 			ON  	C.referencing_database = D.referenced_database
 			AND 	C.referencing_schema 	= D.referenced_schema
 			AND 	C.referencing_name	= D.referenced_name 
 		--  	AND 	C.referencing_server   	= D.referenced_server 
 		--	AND 	C.referencing_id  		= D.referenced_id
 		LEFT OUTER JOIN
 				[SQL-Sicht aus 3.4] 	AS E
 			ON  	D.referencing_database = E.referenced_database
 			AND 	D.referencing_schema 	= E.referenced_schema
 			AND 	D.referencing_name	= E.referenced_name 
 		--  	AND 	D.referencing_server   	= E.referenced_server 
 		--	AND 	D.referencing_id  		= E.referenced_id
 	) 	AS O
ORDER BY 	
 		O.referenced_server
 	, 	O.referenced_database
 	, 	O.referenced_schema 
 	,   	O.referenced_object
 	, 	O.referencing_object1 
 	, 	O.referencing_object2
 	, 	O.referencing_object3 
 	, 	O.referencing_object4
 	, 	O.referencing_object5