sys.columns
Task: listing of the name and nullability of individual columns from the given table
Solution:
SELECT [name], [is_nullable] FROM [sys].[columns] WHERE [object_id] = OBJECT_ID('[dbo].[Test_SysColumns]')
Figure 1: Definition of the table
Figure 2: Result of SQL query
sys.parameters
Task: listing of the I/O parameters from the given stored procedure
Solution:
SELECT [name], [is_output] FROM [sys].[parameters] WHERE [object_id] = OBJECT_ID('[dbo].[Test_SelInputParameters]')
Figure 3: Definition of the stored procedure
Figure 4: Result of SQL query
INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE & INFORMATION_SCHEMA.TABLE_CONSTRAINTS
Task: listing of the name for a primary key (or primary keys) from the given table
Solution: see Figure 6
Figure 5: Definition of the table (see Figure 1)
Figure 6: Definition of SQL query
Figure 7: Result of SQL query
ColumnsDefinition (INFORMATION_SCHEMA.COLUMNS)
Task: listing of the columns name, pre-defined value (suitably adjusted), nullability, data type and detailed information for data type from the given scheme and table. The objective is to provide this information for, e.g. validation logic at the application level of client application
Solution: see Figure 9
Figure 8: Definition of the table
Figure 9: Definition of the stored procedure
Figure 10: Definition of the user defined scalar function
Figure 11: Result of the stored procedure