Get column metadata
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
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.