code

Get column metadata

Robin Michael

Jul 29, 2021 • 1 min read

Putting this here to remember how to query out the metadata of a view, using the AdventureWorks2017 database view vAdditionalContactInfo for reference.

use [AdventureWorks2017]

SELECT [c].[column_id], [c].[name] AS [ColumnName]
FROM [sys].[columns] [c]
INNER JOIN [sys].[views] [v]
ON [c].[object_id] = [v].[object_id]
WHERE [v].[name] LIKE '%vAdditionalContactInfo%'

The goal is to get a reference set of columns and positions so that the dynamic sql that uses the ORDER BY which will vary can be checked against the system metadata and prevent sql injection attacks by comparing positions and column names to known valid entries. Reason to do this in sql is two-fold performing the windowing of the OFFSET and ROWS to get a partial segment of the total records reduces operation cost and keeps queries fast and ordering can occur before transmission back to the clients.

Sign up for more like this.