Catalog Views (U-SQL)
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:
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);