SQL SERVER - write a query to find all tables in a Database that have a specific column name

In this article i am goint to explain write a Scrits to find all tables in a db that have a specific column name. I quickly wrote down following script which will go return all the tables containing specific column along with their schema name.

 

Find All specific column in all tables:

USE[database] 

SELECT 

        tbl.name AS 'TableName', 

        Col.name AS 'ColumnName', 

        SCHEMA_NAME(SCHEMA_ID) AS 'SchemaName'  

FROM  sys.columns AS Col  

INNER  JOIN sys.tables AS tbl ON Col.object_id = tbl.object_id  

WHERE Col.name  =  'questionid' -- Your Column Name  

Find All specific column in all tables and View:

 

USE[database] 

SELECT DISTINCT

       TABLE_CATALOG AS 'DatabaseName',

       TABLE_SCHEMA AS 'SchemaName',

       TABLE_NAME AS 'TableNameAndViewName',

       COLUMN_NAME AS 'ColumnName'

FROM INFORMATION_SCHEMA.columns

WHERE column_name = 'questionid' ---- Your Column Name

SQL SERVER -Get or Find or Retrieve the list all Primary Key, Foreign Key and Unique Key into the Database

In this article I am going to explain How to get or find or retrieve the list all Primary Key, Foreign Key and Unique Key into the database.

There is Two difference method to get/find/retrieve the list all Primary Key, Foreign Key and Unique Key into the database.

Retrive the List of Primary Key using sys.objects: 

-----------------------Get all PRIMARY key into the Database-----------------------------------------

SELECT 
OBJECT_NAME(OBJECT_ID) AS 'PrimaryKeyName',
SCHEMA_NAME(schema_id) AS 'SchemaName',
OBJECT_NAME(parent_object_id) AS 'TableName',
type_desc AS 'ConstraintType/KeyType'
FROM sys.objects 
WHERE type_desc = 'PRIMARY_KEY_CONSTRAINT'

Retrive the List of Foreign Key using sys.objects: 

-------------------------Get all FOREIGN key into the Database--------------------------------------

SELECT 
OBJECT_NAME(OBJECT_ID) AS 'PrimaryKeyName',
SCHEMA_NAME(schema_id) AS 'SchemaName',
OBJECT_NAME(parent_object_id) AS 'TableName',
type_desc AS 'ConstraintType/KeyType'
FROM sys.objects 
WHERE type_desc = 'FOREIGN_KEY_CONSTRAINT'

Retrive the List of Unique Key using sys.objects: 

-------------------------Get all UNIQUE key into the Database--------------------------------------

SELECT 
OBJECT_NAME(OBJECT_ID) AS 'PrimaryKeyName',
SCHEMA_NAME(schema_id) AS 'SchemaName',
OBJECT_NAME(parent_object_id) AS 'TableName',
type_desc AS 'ConstraintType/KeyType'
FROM sys.objects 
WHERE type_desc = 'UNIQUE_CONSTRAINT'

Retrive the List of Primary/Foreign/Unique Key using INFORMATION_SCHEMA.KEY_COLUMN_USAGE View: 

-------Get all Primary Key, UniqueKay and FOREIGN Key into the database using view---------

SELECT [CONSTRAINT_CATALOG] AS 'DatabaseName'
      ,[CONSTRAINT_SCHEMA] AS 'KeySchemaName'
      ,[CONSTRAINT_NAME] AS 'KeyName'
      ,[TABLE_NAME] AS 'TableName'
      ,[COLUMN_NAME] AS 'ColumnName'
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE 

SQL Server - Like search record any value from column to stored in comma separate value(CSV).

In this blog I am going to explain SQL Server - Like search record any value from column to stored in comma separate value(CSV).
Example:  I have stored data to comma separated value into the column : '1,25,11,21,41,61,91,22'. So I want to search only 1 stored all column not apart from 1 like 11,21 etc.

There is one tricky scenario. If I am looking for '1' in the list '1,25,11,21,41,61,91,22' then it would find ",1" and return that incorrect entry. This takes care of all solutions:

--------Search record to any value in categoryId which is store in CSV-----------
DECLARE @Search VARCHAR(10)
SET @Search = '1'

SELECT [Id] ,[Name] ,[CategoryId] FROM [tblStudents] 

WHERE ','+ [CategoryId] + ',' like '%,' + @Search + ',%'

SQL SERVER- Script to find or get all indexes on table with columns name in the database

In this article i am goint to explain write a Scrits to find all the indexes that have included columns in it and the name of the table to which the index belongs to

SELECT  

          S.NAME AS 'SchemaName',   

          T.NAME AS 'TableName',   

           I.NAME AS 'IndexName',  

          C.NAME AS 'ColumnName',  

           i.type_desc AS 'IndexType',   

           CASE WHEN I.is_primary_key = 1 THEN 'Yes' ELSE 'No' END AS 'IsPrimaryKey'  

FROM SYS.TABLES AS T  

INNER JOIN   SYS.SCHEMAS   AS   S   ON T.SCHEMA_ID   =   S.SCHEMA_ID  

INNER JOIN   SYS.INDEXES     AS I     ON    I.OBJECT_ID     =   T.OBJECT_ID  

INNER JOIN   SYS.INDEX_COLUMNS    AS    IC    ON IC.OBJECT_ID   =   T.OBJECT_ID  

INNER JOIN   SYS.COLUMNS       AS    C    ON C.OBJECT_ID  = T.OBJECT_ID AND IC.INDEX_ID  = I.INDEX_ID   

AND IC.COLUMN_ID  =   C.COLUMN_ID   

WHERE 1 = 1 ORDER BY I.NAME