Archive for August, 2010

SysObjects

Posted: August 15, 2010 in MSSQL
Tags:

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 1: Definition of the table

Figure 2: Result of SQL query
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 3: Definition of the stored procedure

Figure 4: Result of SQL query
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 6: Definition of SQL query

Figure 7: Result 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 8: Definition of the table

Figure 9: Definition of the stored procedure
Figure 9: Definition of the stored procedure

Figure 10: Definition of the user defined scalar function
Figure 10: Definition of the user defined scalar function

Figure 11: Result of the stored procedure
Figure 11: Result of the stored procedure

Advertisements