Find all tables containing particular column

There are two ways to retrieve all the tables containing a particuler column

1) using INFORMATION_SCHEMA

SELECT * FROM INFORMATION_SCHEMA.COLUMNS As i WHERE i.column_name LIKE '%MyField%'

2) using SYS.TABLES

SELECT * FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE '%MyField%'

If you know the exact column name, then use = instead of LIKE to reduce the number of unrelated rows return

How to get sql table column names based on column field value

There is no easy way to retrieve a list of columns from a particular SQL Server table based on the column values. The only way is to create a user-defined function or a stored procedure and then retrieve its results.

Here is an example of stored procedure that returns a comma separated list of table columns that contain a particular value.

CREATE PROCEDURE [dbo].[usp_FindColumnsContainingTheValue](@table_name nvarchar(50), @val nvarchar(50))
AS
BEGIN
DECLARE @column_name nvarchar(50)
DECLARE @column_list nvarchar(1000)
DECLARE @count int
DECLARE @sql nvarchar(1000)

DECLARE my_cursor CURSOR FOR
SELECT column_name 
FROM information_schema.columns
WHERE table_name=@table_name
AND data_type IN('nvarchar','varchar', 'ntext', 'nchar')

OPEN my_cursor

FETCH NEXT FROM my_cursor
INTO @column_name

SET @column_list=''

WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql=N'SELECT @countOUT=COUNT(*) FROM ' + @table_name + ' WHERE ' + @column_name + '=' + '''' + @val + ''''

EXEC sp_executesql 
        @query = @sql, 
        @params = N'@countOUT INT OUTPUT', 
        @countOUT = @count OUTPUT

 IF @count > 0 
 BEGIN
  IF LEN(@column_list) > 0
  BEGIN
   SET @column_list=@column_list + ',' + @column_name
  END
  ELSE
  BEGIN
   SET @column_list= @column_name
  END


 END
     FETCH NEXT FROM my_cursor
     INTO @column_name
END

CLOSE my_cursor
DEALLOCATE my_cursor
SELECT @column_list

END

As you can see, the stored procedure I wrote takes two parameters - table name and a value.

Now, as a way to illustrate on how this stored procedure can be used, I will take Northwind database table Orders and will use the above procedure to retrieve all the columns that contain value 'Brazil'.

EXECUTE dbo.usp_FindColumnsContainingTheValue 'Orders', 'Brazil'

Will return a single value 'ShipCountry', for it's the only column that contains a value 'Brazil' in one of the rows.

How to retrieve all the constraints on a particular column in a specific table?

The best way is to use INFORMATION_SCHEMA, INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME column in particular. Here is an example using Northwind database, Products table and ProductName column. SQL below will retrieve all the constraints on ProductName column:

DECLARE @tablename nvarchar(50)
DECLARE @column_name nvarchar(50)

SET @tablename = 'Products'
SET @column_name = 'ProductName'

SELECT INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_NAME 
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS  
INNER JOIN INFORMATION_SCHEMA.COLUMNS ON INFORMATION_SCHEMA.TABLE_CONSTRAINTS.TABLE_NAME = INFORMATION_SCHEMA.COLUMNS.TABLE_NAME
WHERE INFORMATION_SCHEMA.TABLE_CONSTRAINTS.TABLE_NAME =@tablename
AND INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME=@columnname 

Constraints and how to disable or drop all the constraints on a specific table?

Sometimes constrains in the table can get in the way, like for instance when one needs to load initial values into a database one table at a time, without worrying with foreign key constraints and checks until all of the tables have finished loading. In that case one might want to delete or better, disable, all the constraints before performing the task, and then re-enabling them.

Although there is one catch with disabling constraints - you can only disable FOREIGN KEY constraint and the CHECK constraint. PRIMARY KEY, UNIQUE, and DEFAULT constraints are always active.


Below are two examples of how to delete and disable all the contstraints on a specific table respectively. I used Northwind database as an example.

There are numbers of ways to delete all the constraints, including deleting them from the system tables, but it's better to create a script that will get all the constraints from INFORMATION_SCHEMA view and then dynamically delete them, rather then deleting them directly from the system tables.

DECLARE @database nvarchar(50)
DECLARE @table nvarchar(50)

SET @database = 'Northwind'
SET @table = 'Products'

DECLARE @sql nvarchar(255)
WHILE EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog = @database AND table_name = @table)
BEGIN
    SELECT @sql = 'ALTER TABLE ' + @table + ' DROP CONSTRAINT ' + CONSTRAINT_NAME 
    FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS 
    WHERE constraint_catalog = @database and table_name AND @table
    EXEC sp_executesql @sql
END

The above will delete all the constraints for Products table in the Northwind database.

And here's how one can disable all the constraints on a particular table:

DECLARE @database nvarchar(50)
DECLARE @table nvarchar(50)

SET @database = 'Northwind'
SET @table = 'Products'

DECLARE @sql nvarchar(255)
WHILE EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog = @database AND table_name = @table)
BEGIN
    SELECT @sql = 'ALTER TABLE ' + @table + ' NOCHECK CONSTRAINT ' + CONSTRAINT_NAME 
    FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS 
    WHERE constraint_catalog = @database AND table_name = @table
    EXEC sp_executesql @sql
END

For disabling all constraints at once there is a simpler way without going through constraints one by one:

DECLARE @database nvarchar(50)
DECLARE @table nvarchar(50)

SET @database = 'Northwind'
SET @table = 'Products'

DECLARE @sql nvarchar(255)

SELECT @sql = 'ALTER TABLE ' + @table + ' NOCHECK CONSTRAINT ALL'
EXEC sp_executesql @sql

What is INFORMATION_SCHEMA?

System views are predefined Microsoft created views for extracting SQL Server metadata. System Views can be found under System Databases -> master -> Views -> System Views.
The first group of System Views belongs to the Information Schema set. INFORMATION_SCHEMA contains 20 different views. Most of the Information Schema view names are self-explanatory. For example INFORMATION_SCHEMA.TABLES returns a row for each table. INFORMATION_SCHEMA.COLUMNS returns a row for each column.

INFORMATION_SCHEMA is contained in each database and each INFORMATION_SCHEMA view contains meta data for all data objects stored in that particular database. For example one can retirieve all the constraint information on a particular table etc.

How to pass a table name as a parameter to a stored procedure

In order to pass table name along with some fields to a stored procedure that does insert, one has to take advantage of dynamic sql

CREATE PROCEDURE myProc
@TableName as VARCHAR(50),
@fieldlist as VARCHAR(100),
@ID as INT, 
@Name as VARCHAR(50)
AS 
DECLARE @sql VARCHAR(1000) 
DECLARE @str NVARCHAR(1000)

SET @sql = 'INSERT INTO ' + @TableName + '(' + @fieldlist + ')' + ' Values(@ID,@Name)'
Select @str = CAST(@sql as NVarchar(1000))
EXECUTE sp_executesql @str,N'@ID INT,@Name VARCHAR(50)', @ID, @Name

GO

The above stored procedure can be called with the following

EXEC myProc 'Table1', 'ID, Name' ,1,'Taylor Lautner'
GO