Table of contents
TOC
Collapse the table of content
Expand the table of content

Catalog Views (U-SQL)

Michael Rys|Last Updated: 5/15/2018
|
4 Contributors

Catalog views return information that is used by U-SQL. We recommend that you use catalog views because they are the most general interface to the catalog metadata and provide the most efficient way to obtain, transform, and present customized forms of this information.

Catalog views will not contain objects that have been created as part of the same script and will only show the objects that the user submitting the query has the rights to see.

The U-SQL catalog views are currently not available in the local run environment.

Future releases will add additional catalog views.

The following catalog views are currently available:

Built-in catalog views
usql.columns
usql.partition_parameters
usql.databases
usql.partition_range_values
usql.distributions
usql.schemas
usql.distribution_columns
usql.stats
usql.functions
usql.stats_columns
usql.indexes
usql.tables
usql.index_columns
usql.types
usql.objects
usql.views
usql.partitions

Examples

The examples can be executed in Visual Studio with the Azure Data Lake Tools plug-in.

Query the usql.databases view

USE TestReferenceDB;

OUTPUT usql.databases
TO "/ReferenceGuide/CatalogViews/databases.txt"
USING Outputters.Tsv(outputHeader:true);

Querying multiple catalog views
The following script returns the tables with their fully qualified, quoted names as well as their column information (name and type and maximal possible field size) ordered alphabetically by table and in order of their column positions:

@result =
   SELECT "[" + db.name + "].[" + s.name + "].[" + t.name + "]" AS table_name,
          c.name AS col_name,
          c.column_id AS col_pos,
          ct.qualified_name AS col_type,
          c.max_length == - 1 ? 
            ct.qualified_name == "System.String" ? 
              128 * 1024 
            : ct.qualified_name == "System.Byte[]" ? 
                4 * 1024 * 1024 
              : - 1 
          : c.max_length AS col_max_length
   FROM usql.databases AS db 
   JOIN usql.schemas AS s ON db.database_id_guid == s.database_id_guid
   JOIN usql.tables AS t ON s.schema_id_guid == t.schema_id_guid
   JOIN usql.columns AS c ON c.object_id_guid == t.object_id_guid
   JOIN usql.types AS ct ON c.type_id_guid == ct.type_id_guid;

 OUTPUT @result
 TO "/ReferenceGuide/CatalogViews/tableinfo.csv"
 ORDER BY table_name, col_pos
 USING Outputters.Csv(outputHeader : true);

See Also

© 2018 Microsoft