Tags: MSSQL

Database is backbone of software. Its more important then any other thing in software industries. Its like gold mine of knowledge of how people working around with their data(Information). All top software companies Google, Facebook, etc... are nothing without database. Programming language could be anything what matter is database.

1. Find tables which contains specific column name.

SELECT  [TABLE_NAME] ,
        [INFORMATION_SCHEMA].COLUMNS.COLUMN_NAME
FROM    INFORMATION_SCHEMA.COLUMNS
WHERE   INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME LIKE '%id%' ;

2. List columns of table and related properties (Default value, Type, etc...)

SELECT [COLUMN_NAME], *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = N'user_master'

3. Drop Primary key constraint.

DECLARE @sql NVARCHAR(MAX);

SELECT @sql = 'ALTER TABLE table_name DROP CONSTRAINT ' + name + ';'
FROM sys.key_constraints
WHERE [type] = 'PK'
AND [parent_object_id] = OBJECT_ID('table_name');

EXEC sp_executeSQL @sql;

4. ISNULL to replace with value.
To replace value when you are expecting value could be NULL.

DECLARE @NullValue INT = NULL;
/* ISNULL(@variable, ReplaceWithValue) */
SET @NullValue = ISNULL(@NullValue,10)
PRINT @NullValue

5. ISNUMERIC to check value is numeric or not.

DECLARE @NullValue INT = NULL;
/* ISNULL(@variable, ReplaceWithValue) */
SET @NullValue = ISNULL(@NullValue,10)
/* ISNUMERIC(@variable) */
PRINT ISNUMERIC(@NullValue)

6. To check that Index is used (seek) or not for particular table.

/* Change database name and table_name in where condition */
SELECT DISTINCT OBJECT_NAME(sis.OBJECT_ID) TableName,
  si.name AS IndexName, 
  sc.Name AS ColumnName,
  sic.Index_ID, 
  sis.user_seeks, 
  sis.user_scans, 
  sis.user_lookups, 
  sis.user_updates
FROM sys.dm_db_index_usage_stats sis
INNER JOIN sys.indexes si 
 ON sis.OBJECT_ID = si.OBJECT_ID AND sis.Index_ID = si.Index_ID
INNER JOIN sys.index_columns sic 
 ON sis.OBJECT_ID = sic.OBJECT_ID AND sic.Index_ID = si.Index_ID
INNER JOIN sys.columns sc 
 ON sis.OBJECT_ID = sc.OBJECT_ID AND sic.Column_ID = sc.Column_ID
WHERE sis.Database_ID = DB_ID('database_name') AND sis.OBJECT_ID = OBJECT_ID('table_name');

Tags: MSSQL