SQL Server Interview Questions

(1) How To Update Description Value for a Column in Table using SQL Command?
We can Update Description to Column using sp_updateextendedproperty System Store Procedure.
Sample Command to Update Description for Column in a Table:
EXEC sys.sp_updateextendedproperty @name=N'MS_Description', @value=N'My Description for Column Here' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'MyTableName',       @level2type=N'COLUMN',@level2name=N'ColumnName'

(2) How To Delete Description Value for a Column in Table using SQL Command?
We can Delete Description from Column using sp_dropextendedproperty System Store Procedure.
Sample Command to Delete Description from Column in a Table:
EXEC sys.sp_dropextendedproperty @name=N'MS_Description', @value=N'My Description for Column Here' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'MyTableName',       @level2type=N'COLUMN',@level2name=N'ColumnName'

(3) How we can add Description to the Column using Sql Command?
We can Add Description to Column using sp_addextendedproperty System Store Procedure.
Sample Command to Insert Description for Column in a Table:
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'My Description for Column Here' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'MyTableName',       @level2type=N'COLUMN',@level2name=N'ColumnName'

(4) How To Get Description Value for all Column in Table using SQL Command?
To Get Description of Columns we need to use system function sys.fn_listextendedproperty.
Command To Get Description Data for all Columns:
SELECT * FROM   fn_listextendedproperty(NULL, 'SCHEMA',
 'dbo', 'TABLE', 'YourTable Name Here', 'COLUMN', NULL)

(5) How To Get Description Value for Single Column in Table using SQL Command?
To Get Description of Single Column we need to use system function sys.fn_listextendedproperty. we need to pass the column Name is this case.
Command To Get Description Data for Single Columns:
SELECT * FROM   fn_listextendedproperty(NULL, 'SCHEMA',
 'dbo', 'TABLE', 'Table Name Here', 'COLUMN', 'Column Name Here')

(6) How We can get the DB name using SQL Command?
Following is the Command to get the DB name using Command analyzer
SELECT DB_NAME()

(7) What is the use of Set NOCOUNT ON;?
By Default When we execute any command it return us the number of record affected. if we don't want to return the number of records affected then we can use
SET NOCOUNT ON;

0 comments: