SQL Script: Automatic Generate Column Defination

Hi everyone, Today I've some tip to show you the way for generate column information in table in Microsoft SQL Server.

Sample output will show detail column name, data type, collation type and more.






This SQL query script will retrieve all of information of all Table in database used.

I hope this script will help you!!!


SET NOCOUNT ON
GO

CREATE TABLE #myTable99(TABLE_NAME varchar(256), TBL_ROWS int)
GO

DECLARE myCursor99 CURSOR
FOR
SELECT 'SELECT ' + '''' + TABLE_NAME + '''' + ', COUNT(*) AS TBL_ROWS FROM ' + TABLE_NAME AS SQL
FROM INFORMATION_SCHEMA.Tables WHERE TABLE_TYPE = 'BASE TABLE'

DECLARE @SQL varchar(8000)

OPEN myCursor99

FETCH NEXT FROM myCursor99 INTO @SQL

WHILE @@FETCH_STATUS = 0
  BEGIN
    INSERT INTO #myTable99(TABLE_NAME, TBL_ROWS)
    EXEC(@SQL)
    FETCH NEXT FROM myCursor99 INTO @SQL
  END

CLOSE myCursor99
DEALLOCATE myCursor99

    SELECT t.TBL_ROWS, c.*
      FROM INFORMATION_SCHEMA.Columns c
INNER JOIN  #myTable99 t 
        ON c.TABLE_NAME = t.TABLE_NAME collate SQL_Latin1_General_CP1_CI_AS
     WHERE TBL_ROWS <> 0
GO

DROP TABLE #myTable99
SET NOCOUNT OFF
GO