To Generate Table Structure in Excel Sheet

This blog help the people who want to generate a excel sheet of the all table structure for specified database. Following is the procedure which generate a Excel sheet in your D drive for all table structure of specified database.

Before Executing this procedure you have to follow following settings.

1).Microsoft SQL Server 2005 -> Configuration Tools -> Surface Area Configuration -> Surface Area Configuration for Features

2) Mark True on Enable Web Assistant.

Or

Run Following query in sql server which will enable web assistant procedures.

sp_configure 'show advanced options', 1;

GO

RECONFIGURE;

GO

sp_configure 'Web Assistant Procedures', 1;

GO

RECONFIGURE

GO

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

CREATE PROCEDURE [dbo].[SP_CREATE_EXCEL_STRUCTURE_FOR_ALLDATABASE]

(

@db_name VARCHAR(100)

)

AS

BEGIN

DECLARE @columns VARCHAR(8000), @sql VARCHAR(8000), @data_file VARCHAR(100)

DECLARE @file_name varchar(100),@table_name VARCHAR(100)

DECLARE @Constraint VARCHAR(50)

DECLARE C1 CURSOR FOR select name from sys.tables

OPEN C1

FETCH NEXT FROM C1 INTO @table_name

WHILE @@FETCH_STATUS=0

BEGIN

drop TABLE MYTMP

SELECT @Constraint=B.COLUMN_NAME

FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS A,

INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE B

WHERE CONSTRAINT_TYPE = 'PRIMARY KEY'

AND A.CONSTRAINT_NAME = B.CONSTRAINT_NAME

AND A.TABLE_NAME=@table_name

SELECT column_name FieldName,Data_type DataType,

character_maximum_length Size,is_nullable AllowNulls

INTO MYTMP FROM information_schema.columns

WHERE table_name=@table_name

ALTER TABLE MYTMP ADD PrimaryKey VARCHAR(20)

ALTER TABLE MYTMP ADD ForeignKey VARCHAR(70)

ALTER TABLE MYTMP ADD AutoIncrement VARCHAR(20)

ALTER TABLE MYTMP ADD DefaultValue VARCHAR(50)

ALTER TABLE MYTMP ADD Description VARCHAR(500)

UPDATE MYTMP SET PrimaryKey='YES' WHERE FieldName=@Constraint

UPDATE MYTMP SET PrimaryKey='' WHERE FieldName<>@Constraint

---=================To Add Foreign key=================================

UPDATE MYTMP SET ForeignKey=Tmp.ForeignKey

From

(SELECT COL_NAME(fc.parent_object_id,fc.parent_column_id) AS ColumnName,

OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName,

COL_NAME(fc.referenced_object_id,fc.referenced_column_id)

AS ReferenceColumnName,

OBJECT_NAME (f.referenced_object_id)+

'('+COL_NAME(fc.referenced_object_id,fc.referenced_column_id)+')'

As ForeignKey

FROM sys.foreign_keys AS f

INNER JOIN sys.foreign_key_columns AS fc ON

f.OBJECT_ID = fc.constraint_object_id

WHERE OBJECT_NAME(f.parent_object_id)=@TABLE_NAME)As Tmp

WHERE FieldName=Tmp.ColumnName

UPDATE MYTMP SET ForeignKey='' WHERE ForeignKey IS NULL

----======================To Set Default Value===============================

UPDATE MYTMP SET DefaultValue=Tmp.TEXT

FROM

(SELECT name,REPLACE(REPLACE(TEXT,'(',''),')','')TEXT FROM syscolumns

inner join syscomments on(syscomments.id=syscolumns.cdefault)

WHERE syscolumns.id = object_id(@Table_name) AND cdefault > 0)As Tmp

WHERE FieldName=Tmp.Name

UPDATE MYTMP SET DefaultValue='' WHERE DefaultValue IS NULL

----======================= To set Auto Increment============================

UPDATE MYTMP SET AutoIncrement='YES'

FROM

(SELECT NAME AS COLUMNNAME FROM SYS.IDENTITY_COLUMNS

WHERE OBJECT_NAME(OBJECT_ID)=@Table_name)AS TMP

WHERE FieldName=Tmp.ColumnName

UPDATE MYTMP SET AutoIncrement='' WHERE AutoIncrement IS NULL

----========================To Set Description ==============================

UPDATE MYTMP SET Description=Tmp.Description

FROM

(SELECT convert(varchar(500),ep.value) AS Description,c.name AS ColumnName

FROM sys.objects o

INNER JOIN sys.extended_properties ep ON o.object_id = ep.major_id

INNER JOIN sys.schemas s ON o.schema_id = s.schema_id

LEFT JOIN syscolumns c ON ep.minor_id = c.colid AND ep.major_id = c.id

WHERE o.type='U' and o.name=@table_name)As Tmp

Where FieldName=Tmp.ColumnName

UPDATE MYTMP SET Description='' WHERE Description IS NULL

---===============================================================

SET @file_name='D:\'+@table_name+'.xls'

--===================================

exec sys.sp_makewebtask @outputfile = @file_name,

@query = 'Select * from mytmp',

@colheaders =1,

@FixedFont=0,@lastupdated=1,@resultstitle=@table_name,

@dbname =@db_name

--========================================

FETCH NEXT FROM C1 INTO @table_name

END

CLOSE C1

DEALLOCATE C1

END

------------------TO EXECUTE

--SP_CREATE_EXCEL_STRUCTURE_FOR_ALLDATABASE 'jito'