Finding Column Name from all the tables

In some situation the SQL Developers/Administrators required the column name from all the tables in a database. The below query batch will help to find all such columns in the database:

Declare @SearchString nvarchar(100)

SET  @SearchString=‘Search String should be entered here’

CREATE TABLE #Results (TableName nvarchar(370), ColumnName nvarchar(3630))

SET NOCOUNT ON

DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128),

 @SearchString2 nvarchar(110) 

 SET  @TableName = ”   

 SET @SearchString2 =

 QUOTENAME(‘%’ + @SearchString + ‘%’,””)

WHILE @TableName IS NOT NULL   

BEGIN      

  SET @ColumnName = ”     

  SET @TableName =  (

    SELECT MIN(QUOTENAME(TABLE_SCHEMA) + ‘.’ +

    QUOTENAME(TABLE_NAME)) FROM INFORMATION_SCHEMA.TABLES

    WHERE

    TABLE_TYPE = ‘BASE TABLE’

    AND QUOTENAME(TABLE_SCHEMA) + ‘.’ + QUOTENAME(TABLE_NAME) > @TableName

    AND OBJECTPROPERTY(

      OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + ‘.’ + QUOTENAME(TABLE_NAME)),

        ‘IsMSShipped’) = 0)

  WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)     

  BEGIN

    SET @ColumnName = (

      SELECT MIN(QUOTENAME(COLUMN_NAME))

      FROM INFORMATION_SCHEMA.COLUMNS

      WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2)

        AND TABLE_NAME = PARSENAME(@TableName, 1)

      AND DATA_TYPE IN (‘char’, ‘varchar’, ‘nchar’, ‘nvarchar’)

      AND QUOTENAME(COLUMN_NAME) > @ColumnName)

      IF @ColumnName IS NOT NULL           

      BEGIN

      INSERT INTO #Results

      EXEC

      (

        ‘SELECT ”’ + @TableName + ‘.’ + @ColumnName + ”’, LEFT(‘ + @ColumnName +

          ‘, 3630) FROM ‘ + @TableName + ‘ (NOLOCK) ‘ +

        ‘ WHERE ‘ + @ColumnName + ‘ LIKE ‘ + @SearchString2

      )            

      END      

    END    

  END

  SELECT TableName, ColumnName FROM #Results

How to Enable/Disable the Identity column?

To insert the values manually in to the identity column you have to enable by setting IDENTITY_INSERT to ON. To stop inserting the manual values into identity column you have to disable by setting IDENTITY_INSERT to OFF. Here is the script:

SET IDENTITY_INSERT <Table Name> ON; — This will enable the Identity column inserting values
<Insert Query with identity column values>
SET IDENTITY_INSERT <Table Name> OFF; — This will disable the Identity column inserting values

How to reseed the Identity column in a table?

Here is the syntax to reseed the Identity column in a table:

DBCC CHECKIDENT (‘<table Name>’,RESEED,<value>)

Eg.
DBCC CHECKIDENT (‘table1’,RESEED,10) — If you want to insert the value from 11

How to find the duplicate rows in a table

To find the duplicate rows in a table we can use the following SQL query:

SELECT
Column1,Column2,COUNT(PK_Column)
FROM
<Table Name>
GROUP BY
Column1,Column2
HAVING
COUNT(ColumnWithDuplicateValues) > 1