In SQL Server, is there an easy way to output a list of fully qualified field names from a SELECT statement, even with aliases?

For example:

SELECT
      a.FieldName1
    , a.FieldName2
    , CASE WHEN a.FieldName9 = 'A' and b.FieldName10 = 'C' Then 'Yes' Else 'No' END as AliasedCase
FROM DatabaseA.dbo.Table1 a
JOIN DatabaseB.dbo.Table2 b on a.FieldName4 = b.FieldName4
WHERE b.FieldName8 Between 1 and 4

Output list would look like:

1. DatabaseA.dbo.Table1.FieldName1
2. DatabaseA.dbo.Table1.FieldName2
3. DatabaseA.dbo.Table1.FieldName4
4. DatabaseA.dbo.Table1.FieldName9
5. DatabaseB.dbo.Table2.FieldName4
6. DatabaseB.dbo.Table2.FieldName8
7. DatabaseB.dbo.Table2.FieldName10

CodePudding user response：

You can create a view or procedure with that SELECT, use the sys.dm_sql_referenced_entities function to get the information, then drop it afterwards.

create view dbo.MyTempView
as

SELECT
      a.FieldName1
    , a.FieldName2
    , CASE WHEN a.FieldName9 = 'A' and b.FieldName10 = 'C' Then 'Yes' Else 'No' END as AliasedCase
FROM DatabaseA.dbo.Table1 a
JOIN DatabaseB.dbo.Table2 b on a.FieldName4 = b.FieldName4
WHERE b.FieldName8 Between 1 and 4

select CONCAT(
  dep.referenced_server_name   '.',
  dep.referenced_database_name   '.',
  dep.referenced_schema_name   '.',
  dep.referenced_entity_name   '.',
  dep.referenced_minor_name)
from sys.dm_sql_referenced_entities('dbo.MyTempView', 'OBJECT') dep
where dep.referenced_minor_name is not null;

drop view dbo.MyTempView;

The use of CONCAT along with '.' means that nulls will be ignored along with their associated ., so it will only tell you the actual reference you used, it will not work out which database or schema is used if you did not specify it (due to scoping issues, as it depends what your default database and schema is when executed).

CodePudding user response：

Try as follows:

SELECT 
    c.object_id,
    o.name AS ObjName,
    c.name FieldName,
    c.column_id,
    c.system_type_id,
    c.user_type_id,
    c.max_length,
    c.precision,
    c.scale,
    c.collation_name,
    c.is_nullable,
    c.is_ansi_padded,
    c.is_rowguidcol,
    c.is_identity,
    c.is_computed,
    c.is_filestream,
    c.is_replicated,
    c.is_non_sql_subscribed,
    c.is_merge_published,
    c.is_dts_replicated,
    c.is_xml_document,
    c.xml_collection_id,
    c.default_object_id,
    c.rule_object_id,
    c.is_sparse,
    c.is_column_set,
    c.generated_always_type,
    c.generated_always_type_desc,
    c.encryption_type,
    c.encryption_type_desc,
    c.encryption_algorithm_name,
    c.column_encryption_key_id,
    c.column_encryption_key_database_name,
    c.is_hidden,
    c.is_masked,
    c.graph_type,
    c.graph_type_desc , 
    o.object_id,
    o.principal_id,
    o.schema_id,
    o.parent_object_id,
    o.type,
    o.type_desc,
    o.create_date,
    o.modify_date,
    o.is_ms_shipped,
    o.is_published,
    o.is_schema_published 
FROM 
    sys.all_columns c 
INNER JOIN 
    sys.all_objects o ON o.object_id = c.object_id

With that query you can get all table and other objects with column names of your database. Then you can filter as you want.