System Views and Functions in SQL Server 2005
I tried to explore some basic TSQL functions and VIEWS in SQL Server 2005.
I believe this would be useful when we write DDL-DML Statements.
declare @schemaName varchar(100)
declare @tblName varchar(100)
declare @colName varchar(100)
declare @objectID int
set @schemaName = 'College'
set @tblName = 'Student'
set @colName = 'iStatusID'
set @objectID = OBJECT_ID(@schemaName + '.' + @tblName)
-- 1. How to check the Column is nullable?
SELECT COLUMNPROPERTY(@objectID,@colName,'AllowsNull') AS 'Allows Null?';
-- 2. How to check the Column is an identity?
SELECT COLUMNPROPERTY(@objectID,@colName,'IsIdentity') AS 'Identity?';
-- 3. How to check the Column is an FullTextEnabled?
SELECT COLUMNPROPERTY(@objectID,@colName,'IsFulltextIndexed') AS 'FullTextEnabled?';
-- 4. How to check the Column's datatype?
select b.name as 'Datatype', a.max_length, a.precision, a.scale
from sys.columns a
join sys.types b on a.system_type_id = b.system_type_id and a.user_type_id = b.user_type_id
where OBJECT_NAME(a.object_id) = @tblName and a.name = @colName
-- 5. How to identify that the default constraints have been created for the Column?
select a.name as 'Default Constraint Name'
from sys.default_constraints a
join sys.all_columns b on a.parent_object_id = b.object_id and a.parent_column_id = b.column_id
where object_name(parent_object_id) = @tblName and b.name = @colName
-- 6. How to identify that the foreignkey constraints for the table?
select name as 'ForeignKeyConstraint Name' from sys.objects
where type = 'F' and parent_object_id = @objectID
-- 7. Select all Constraints associated with this table and Column.
SELECT Constraint_Name from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
WHERE Table_Name = @tblName and Column_Name = @colName
-- 8. Find all the Objects from sys.objects table by specifying the "type".
select name from sys.objects WHERE parent_object_id = @objectID and type = 'D'
Note:
By changing the object type, the following objects can be seen.
· PK = PRIMARY KEY constraint
· C = CHECK constraint
· D = DEFAULT (constraint or stand-alone)
· F = FOREIGN KEY constraint
· UQ = UNIQUE constraint
· AF = Aggregate function (CLR)
· FS = Assembly (CLR) scalar function
· FT = Assembly (CLR) table-valued function
· PC = Assembly (CLR) stored procedure
· FN = SQL scalar function
· IF = SQL inlined table-valued function
· TF = SQL table-valued-function
· R = Rule (old-style, stand-alone)
· SN = Synonym
· TR = SQL DML trigger
· U = Table (user-defined)
· P = SQL stored procedure
· V = View
· X = Extended stored procedure
· IT = Internal table
I believe this would be useful when we write DDL-DML Statements.
declare @schemaName varchar(100)
declare @tblName varchar(100)
declare @colName varchar(100)
declare @objectID int
set @schemaName = 'College'
set @tblName = 'Student'
set @colName = 'iStatusID'
set @objectID = OBJECT_ID(@schemaName + '.' + @tblName)
-- 1. How to check the Column is nullable?
SELECT COLUMNPROPERTY(@objectID,@colName,'AllowsNull') AS 'Allows Null?';
-- 2. How to check the Column is an identity?
SELECT COLUMNPROPERTY(@objectID,@colName,'IsIdentity') AS 'Identity?';
-- 3. How to check the Column is an FullTextEnabled?
SELECT COLUMNPROPERTY(@objectID,@colName,'IsFulltextIndexed') AS 'FullTextEnabled?';
-- 4. How to check the Column's datatype?
select b.name as 'Datatype', a.max_length, a.precision, a.scale
from sys.columns a
join sys.types b on a.system_type_id = b.system_type_id and a.user_type_id = b.user_type_id
where OBJECT_NAME(a.object_id) = @tblName and a.name = @colName
-- 5. How to identify that the default constraints have been created for the Column?
select a.name as 'Default Constraint Name'
from sys.default_constraints a
join sys.all_columns b on a.parent_object_id = b.object_id and a.parent_column_id = b.column_id
where object_name(parent_object_id) = @tblName and b.name = @colName
-- 6. How to identify that the foreignkey constraints for the table?
select name as 'ForeignKeyConstraint Name' from sys.objects
where type = 'F' and parent_object_id = @objectID
-- 7. Select all Constraints associated with this table and Column.
SELECT Constraint_Name from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
WHERE Table_Name = @tblName and Column_Name = @colName
-- 8. Find all the Objects from sys.objects table by specifying the "type".
select name from sys.objects WHERE parent_object_id = @objectID and type = 'D'
Note:
By changing the object type, the following objects can be seen.
· PK = PRIMARY KEY constraint
· C = CHECK constraint
· D = DEFAULT (constraint or stand-alone)
· F = FOREIGN KEY constraint
· UQ = UNIQUE constraint
· AF = Aggregate function (CLR)
· FS = Assembly (CLR) scalar function
· FT = Assembly (CLR) table-valued function
· PC = Assembly (CLR) stored procedure
· FN = SQL scalar function
· IF = SQL inlined table-valued function
· TF = SQL table-valued-function
· R = Rule (old-style, stand-alone)
· SN = Synonym
· TR = SQL DML trigger
· U = Table (user-defined)
· P = SQL stored procedure
· V = View
· X = Extended stored procedure
· IT = Internal table