152 lines
4.5 KiB
SQL
152 lines
4.5 KiB
SQL
/*
|
|
M0 SQL Server Schema Export
|
|
|
|
Ziel:
|
|
- Tabellen, Spalten, Defaults, Primary Keys, Foreign Keys, Indizes und Rowcounts
|
|
aus der Legacy-Datenbank exportieren.
|
|
|
|
Hinweise:
|
|
- In der Ziel-Datenbank ausführen.
|
|
- Ergebnisse als CSV/Excel speichern und nicht mit Produktiv-Secrets ins Repo
|
|
legen.
|
|
- Falls nur Kaffeelisten-Tabellen exportiert werden sollen, den WHERE-Block bei
|
|
Bedarf auf kl_% und CoffeeSurvey% begrenzen.
|
|
*/
|
|
|
|
-- 1. Tabellen und Spalten
|
|
SELECT
|
|
s.name AS schema_name,
|
|
t.name AS table_name,
|
|
c.column_id,
|
|
c.name AS column_name,
|
|
ty.name AS data_type,
|
|
c.max_length,
|
|
c.precision,
|
|
c.scale,
|
|
c.is_nullable,
|
|
c.is_identity,
|
|
dc.definition AS default_definition,
|
|
ep.value AS column_description
|
|
FROM sys.tables t
|
|
JOIN sys.schemas s ON s.schema_id = t.schema_id
|
|
JOIN sys.columns c ON c.object_id = t.object_id
|
|
JOIN sys.types ty ON ty.user_type_id = c.user_type_id
|
|
LEFT JOIN sys.default_constraints dc
|
|
ON dc.parent_object_id = t.object_id
|
|
AND dc.parent_column_id = c.column_id
|
|
LEFT JOIN sys.extended_properties ep
|
|
ON ep.major_id = t.object_id
|
|
AND ep.minor_id = c.column_id
|
|
AND ep.name = 'MS_Description'
|
|
WHERE t.is_ms_shipped = 0
|
|
ORDER BY s.name, t.name, c.column_id;
|
|
|
|
-- 2. Primary Keys und Unique Constraints
|
|
SELECT
|
|
s.name AS schema_name,
|
|
t.name AS table_name,
|
|
kc.name AS constraint_name,
|
|
kc.type_desc,
|
|
ic.key_ordinal,
|
|
c.name AS column_name
|
|
FROM sys.key_constraints kc
|
|
JOIN sys.tables t ON t.object_id = kc.parent_object_id
|
|
JOIN sys.schemas s ON s.schema_id = t.schema_id
|
|
JOIN sys.index_columns ic
|
|
ON ic.object_id = kc.parent_object_id
|
|
AND ic.index_id = kc.unique_index_id
|
|
JOIN sys.columns c
|
|
ON c.object_id = ic.object_id
|
|
AND c.column_id = ic.column_id
|
|
ORDER BY s.name, t.name, kc.name, ic.key_ordinal;
|
|
|
|
-- 3. Foreign Keys
|
|
SELECT
|
|
sch_parent.name AS parent_schema,
|
|
parent_t.name AS parent_table,
|
|
parent_c.name AS parent_column,
|
|
fk.name AS foreign_key_name,
|
|
sch_ref.name AS referenced_schema,
|
|
ref_t.name AS referenced_table,
|
|
ref_c.name AS referenced_column,
|
|
fk.delete_referential_action_desc,
|
|
fk.update_referential_action_desc
|
|
FROM sys.foreign_keys fk
|
|
JOIN sys.foreign_key_columns fkc ON fkc.constraint_object_id = fk.object_id
|
|
JOIN sys.tables parent_t ON parent_t.object_id = fkc.parent_object_id
|
|
JOIN sys.schemas sch_parent ON sch_parent.schema_id = parent_t.schema_id
|
|
JOIN sys.columns parent_c
|
|
ON parent_c.object_id = fkc.parent_object_id
|
|
AND parent_c.column_id = fkc.parent_column_id
|
|
JOIN sys.tables ref_t ON ref_t.object_id = fkc.referenced_object_id
|
|
JOIN sys.schemas sch_ref ON sch_ref.schema_id = ref_t.schema_id
|
|
JOIN sys.columns ref_c
|
|
ON ref_c.object_id = fkc.referenced_object_id
|
|
AND ref_c.column_id = fkc.referenced_column_id
|
|
ORDER BY parent_schema, parent_table, foreign_key_name;
|
|
|
|
-- 4. Indizes
|
|
SELECT
|
|
s.name AS schema_name,
|
|
t.name AS table_name,
|
|
i.name AS index_name,
|
|
i.type_desc,
|
|
i.is_unique,
|
|
i.is_primary_key,
|
|
i.is_unique_constraint,
|
|
ic.key_ordinal,
|
|
ic.is_included_column,
|
|
c.name AS column_name
|
|
FROM sys.indexes i
|
|
JOIN sys.tables t ON t.object_id = i.object_id
|
|
JOIN sys.schemas s ON s.schema_id = t.schema_id
|
|
JOIN sys.index_columns ic
|
|
ON ic.object_id = i.object_id
|
|
AND ic.index_id = i.index_id
|
|
JOIN sys.columns c
|
|
ON c.object_id = ic.object_id
|
|
AND c.column_id = ic.column_id
|
|
WHERE t.is_ms_shipped = 0
|
|
AND i.name IS NOT NULL
|
|
ORDER BY s.name, t.name, i.name, ic.key_ordinal, ic.index_column_id;
|
|
|
|
-- 5. Rowcounts
|
|
SELECT
|
|
s.name AS schema_name,
|
|
t.name AS table_name,
|
|
SUM(p.row_count) AS row_count
|
|
FROM sys.tables t
|
|
JOIN sys.schemas s ON s.schema_id = t.schema_id
|
|
JOIN sys.dm_db_partition_stats p ON p.object_id = t.object_id
|
|
WHERE t.is_ms_shipped = 0
|
|
AND p.index_id IN (0, 1)
|
|
GROUP BY s.name, t.name
|
|
ORDER BY s.name, t.name;
|
|
|
|
-- 6. Trigger
|
|
SELECT
|
|
s.name AS schema_name,
|
|
t.name AS table_name,
|
|
tr.name AS trigger_name,
|
|
tr.is_disabled,
|
|
OBJECT_DEFINITION(tr.object_id) AS trigger_definition
|
|
FROM sys.triggers tr
|
|
JOIN sys.tables t ON t.object_id = tr.parent_id
|
|
JOIN sys.schemas s ON s.schema_id = t.schema_id
|
|
ORDER BY s.name, t.name, tr.name;
|
|
|
|
-- 7. Stored Procedures und Funktionen mit Kaffeelisten-Bezug
|
|
SELECT
|
|
s.name AS schema_name,
|
|
o.name AS object_name,
|
|
o.type_desc,
|
|
OBJECT_DEFINITION(o.object_id) AS object_definition
|
|
FROM sys.objects o
|
|
JOIN sys.schemas s ON s.schema_id = o.schema_id
|
|
WHERE o.type IN ('P', 'FN', 'IF', 'TF')
|
|
AND (
|
|
OBJECT_DEFINITION(o.object_id) LIKE '%kl_%'
|
|
OR OBJECT_DEFINITION(o.object_id) LIKE '%CoffeeSurvey%'
|
|
)
|
|
ORDER BY s.name, o.name;
|