SQL server: list der Spalten, die von einer Gruppe von databasetabellen gemeinsam genutzt werden

Ich erben einen großen Bericht, den wir automatisieren möchten. Ich glaube, die meisten data sind in einem Data Warehouse, dass ich über SQL server zugreifen kann. Das Problem, das ich habe, ist, dass ich nicht viel Dokumentation (datawörterbuch, Schema, etc.) habe und ich möchte feststellen, welche Felder bei mehreren Tabellen üblich sind. Normalerweise, wenn ich eine list von Tabellen und Spalten zurückgeben möchte, würde ich das Informationsschema abfragen. Doch in diesem Fall bin ich im Grunde auf der search nach einer Abfrage, die so etwas wie ist:

select table_name, column_name from (information_schema, others?) where table_name like 'T_Blah %' and column_names are equal 

Ich weiß, dass ich das durch den objectbrowser erreichen kann, aber sobald ich anfange, in eine größere Anzahl von Tischen zu kommen, würde ich gerne einen schnellen Abfrage-basierten Ansatz zur Identifizierung meiner Felder für den Beitritt.

Ich schätze jede Hilfe, die ich bekommen kann. Ich habe es für eine Weile gegoogelt, aber ich habe keine Lösung gefunden (oder ich war mir nicht bewusst, dass ich einen gefunden hatte).

Neben dem Informationsschema haben Sie auch die Systemtabelle syscolumns. Angenommen, Sie verwenden das ehemalige, so etwas sollte funktionieren:

 select column_name, count(distinct table_name) from information_schema.columns group by column_name having count(distinct table_name) > 1 

Offensichtlich könntest du dich nach Tabellenname / Schema einschränken, wenn du eine Untermenge ansehen möchtest

 SELECT * FROM INFORMATION_SCHEMA.COLUMNS 

Wenn ich dich richtig verstanden habe, kannst du das versuchen:

 SELECT TABLE_NAME, COLUMN_NAME FROM ( SELECT *, COUNT(*) OVER(PARTITION BY COLUMN_NAME) Quant FROM INFORMATION_SCHEMA.COLUMNS) A WHERE TABLE_NAME LIKE 'T_Blah%' AND Quant > 1 ORDER BY COLUMN_NAME, TABLE_NAME 

Dies sollte für sql-server funktionieren. Das ist nur eine Abfrage, die ich zur Hand habe, also ist es ein bisschen mehr als du gebeten hast. Aber ich hielt es für den Fall, dass du die extra Info wünschst :). Einfach entfernen, wenn nicht.

 select distinct columns.name, schemas.name + '.' + tables.name from sys.columns join sys.tables on tables.object_id = columns.object_id join sys.schemas on schemas.schema_id = tables.schema_id where tables.name like '%blah%' order by columns.name, schemas.name + '.' + tables.name 

Dies ergibt die Spaltennamen und datatypen in einer angegebenen Tabelle, die auch in anderen Tabellen erscheinen, gefolgt von einer durch Kommas getrennten list von Tabellen, in denen sie erscheinen:

 SELECT COLUMN_NAME, DATA_TYPE, REPLACE(REPLACE(REPLACE( (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE (COLUMN_NAME = CommonColumns.COLUMN_NAME) AND (DATA_TYPE = CommonColumns.DATA_TYPE) FOR XML PATH('')) , '</TABLE_NAME><TABLE_NAME>', ', '), '<TABLE_NAME>', ''), '</TABLE_NAME>', '') AS Tables FROM (SELECT DISTINCT COLUMN_NAME, DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME LIKE 'T_Blah%') AS CommonColumns WHERE (SELECT COUNT(TABLE_NAME) FROM INFORMATION_SCHEMA.COLUMNS WHERE (COLUMN_NAME = CommonColumns.COLUMN_NAME) AND (DATA_TYPE = CommonColumns.DATA_TYPE)) > 1 

Um dies für alle Tische zu tun, einfach das WHERE TABLE_NAME LIKE 'T_Blah%' entfernen.

Oder wie Aaron empfiehlt, vermeiden INFORMATION_SCHEMA:

 SELECT ColumnName, sys.types.name AS DataType, REPLACE(REPLACE(REPLACE( (SELECT sys.tables.name AS TableName FROM sys.columns INNER JOIN sys.tables ON sys.columns.object_id = sys.tables.object_id WHERE (sys.tables.type = 'U') AND (sys.columns.name = CommonColumns.ColumnName) AND (sys.columns.system_type_id = CommonColumns.system_type_id) FOR XML PATH('')) , '</TableName><TableName>', ', '), '<TableName>', ''), '</TableName>', '') AS Tables FROM (SELECT DISTINCT sys.columns.name AS ColumnName, sys.columns.system_type_id FROM sys.columns INNER JOIN sys.tables ON sys.columns.object_id = sys.tables.object_id WHERE (sys.tables.type = 'U') AND (sys.tables.name LIKE 'T_Blah%')) AS CommonColumns INNER JOIN sys.types ON sys.types.system_type_id = CommonColumns.system_type_id WHERE ((SELECT COUNT(sys.tables.object_id) AS TableCount FROM sys.columns INNER JOIN sys.tables ON sys.columns.object_id = sys.tables.object_id WHERE (sys.tables.type = 'U') AND (sys.columns.name = CommonColumns.ColumnName) AND (sys.columns.system_type_id = CommonColumns.system_type_id)) > 1) ORDER BY CommonColumns.ColumnName, sys.types.name 

Um Beziehungen aus Fremdschlüssel zu erhalten, verwenden Sie:

 SELECT sys.foreign_keys.name AS ForeignKeyName, OBJECT_NAME(sys.foreign_key_columns.parent_object_id) AS ForeignKeyTableName, ForeignKeyColumns.name AS ForeignKeyColumnName, OBJECT_NAME(sys.foreign_key_columns.referenced_object_id) AS ReferencedTableName, ReferencedColumns.name AS ReferencedColumnName FROM sys.foreign_keys INNER JOIN sys.foreign_key_columns ON sys.foreign_keys.object_id = sys.foreign_key_columns.constraint_object_id INNER JOIN sys.columns AS ForeignKeyColumns ON sys.foreign_key_columns.parent_object_id = ForeignKeyColumns.object_id AND sys.foreign_key_columns.parent_column_id = ForeignKeyColumns.column_id INNER JOIN sys.columns AS ReferencedColumns ON sys.foreign_key_columns.referenced_object_id = ReferencedColumns.object_id AND sys.foreign_key_columns.referenced_column_id = ReferencedColumns.column_id WHERE (OBJECT_NAME(sys.foreign_key_columns.parent_object_id) LIKE 'T_Blah%') OR (OBJECT_NAME(sys.foreign_key_columns.referenced_object_id) LIKE 'T_Blah%')