This SP will be create C# classes using all table

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[uspSQLObjectsToClass]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[uspSQLObjectsToClass]
GO

/*
Purpose: This SP will be create C# classes using all table in the specified Database
Author: Satyanarayana Bommidi
Created Date: 16th July 2004
Example: EXEC uspSQLObjectsToClass 'Northwind'
Output folder   : c:\SQLClasses
*/
CREATE PROCEDURE uspSQLObjectsToClass (@DBName VARCHAR(100))
AS
BEGIN

SET NOCOUNT ON
--variable declaration
DECLARE @CounterINT,--loop counter
@CountINT,--loop counter
@ObjectNameVARCHAR(100),--to hold Object Name like (Tables, Views, SPs and Triggers)
@ErrorNoINT,--to hold the error number
@ErrorStrVARCHAR(255),--to hold the error description
@TEXTOUTPUTVARCHAR(1000),--to hold the html tags
@FileName VARCHAR(255), --to hold the output file name
@FS INT, --to hold the output parameter of file system
@OLEResult INT, --to hold the result parameter of file system
@FileID INT--to hold the file system object id

--initialization
SELECT @Counter = 0, @Count = 0, @ObjectName = '', @ErrorNo = 0, @ErrorStr = '', 
@TEXTOUTPUT = '', @FileName = '', @FS = 0, @OLEResult = 0 , @FileID = 0


IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE NAME LIKE '#ObjectInfo%')
DROP TABLE #ObjectInfo
IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE NAME LIKE '#ObjectColumns%')
DROP TABLE #ObjectColumns

CREATE TABLE #ObjectInfo(TableID INT, TableName VARCHAR(100), isProcessed BIT)
CREATE TABLE #ObjectColumns(TableID INT, ColumnID INT, ColumnName VARCHAR(100), DataTypeName VARCHAR(50))

SET @ErrorNo = @@Error
IF ISNULL(@ErrorNo, 0) > 0
BEGIN
SELECT @ErrorStr = @ErrorStr + 'Unable to create the temporary tables please check the user permission, Error Number: '+CONVERT(VARCHAR, @ErrorNo), @ErrorNo = 0
GOTO Error_Handler
END

--getting the all table
EXEC ('INSERT INTO #ObjectInfo(TableID, TableName, isProcessed) SELECT id, name, 0 as isProcessed FROM ['+@DBName+'].dbo.SYSOBJECTS WHERE TYPE = ''U'' and name <> ''dtproperties'' ORDER BY name')

SET @ErrorNo = @@Error
IF ISNULL(@ErrorNo, 0) > 0
BEGIN
SELECT @ErrorStr = @ErrorStr + 'Unable to insert records in ObjectInfo table, Error Number: '+CONVERT(VARCHAR, @ErrorNo), @ErrorNo = 0
GOTO Error_Handler
END


--getting the all cloumns
INSERT INTO #ObjectColumns(TableID, ColumnID, ColumnName, DataTypeName) 
SELECT id, colid, col.name, typ.name 
FROM SYSCOLUMNS col 
INNER JOIN SYSTYPES typ on col.xtype = typ.xtype 
INNER JOIN #ObjectInfo obj on col.id = obj.TableID
where typ.name <> 'sysname' order by 1, 2

SET @ErrorNo = @@Error
IF ISNULL(@ErrorNo, 0) > 0
BEGIN
SELECT @ErrorStr = @ErrorStr + 'Unable to insert records in #ObjectColumns table, Error Number: '+CONVERT(VARCHAR, @ErrorNo), @ErrorNo = 0
GOTO Error_Handler
END

--Class files
WHILE EXISTS(SELECT * FROM #ObjectInfo WHERE isProcessed = 0)
BEGIN
SELECT TOP 1 @FileName = TableName, @Counter = TableID, @TEXTOUTPUT = 'using System;' FROM #ObjectInfo WHERE isProcessed = 0
SET @FileName = 'c:\SQLClasses\'+LTRIM(RTRIM(@FileName))+'.cs'
--Deleting the file
EXECUTE @OLEResult = master..xp_fileexist @FileName, @FS OUT

IF @FS > 0 EXECUTE ('EXEC master..xp_CMDShell "Del '+@FileName+'"')

SET @ErrorNo = @@Error
IF ISNULL(@ErrorNo, 0) > 0
BEGIN
SELECT @ErrorStr = @ErrorStr + 'Unable to delete file, Error Number: '+CONVERT(VARCHAR, @ErrorNo), @ErrorNo = 0
GOTO Error_Handler
END

--creating file object
EXECUTE @OLEResult = sp_OACreate 'Scripting.FileSystemObject', @FS OUT

SET @ErrorNo = @@Error
IF ISNULL(@ErrorNo, 0) > 0
BEGIN
SELECT @ErrorStr = @ErrorStr + 'Unable to Create File System Object, Error Number: '+CONVERT(VARCHAR, @ErrorNo), @ErrorNo = 0
GOTO Error_Handler
END

--Open a file
EXECUTE @OLEResult = sp_OAMethod @FS, 'OpenTextFile', @FileID OUT, @FileName, 8, 1
IF @OLEResult <> 0 PRINT 'OpenTextFile'

SET @ErrorNo = @@Error
IF ISNULL(@ErrorNo, 0) > 0
BEGIN
SELECT @ErrorStr = @ErrorStr + 'Unable to Open File, Error Number: '+CONVERT(VARCHAR, @ErrorNo), @ErrorNo = 0
GOTO Error_Handler
END

--Write Text1
EXECUTE @OLEResult = sp_OAMethod @FileID, 'WriteLine', NULL, @TEXTOUTPUT

SET @ErrorNo = @@Error
IF ISNULL(@ErrorNo, 0) > 0
BEGIN
SELECT @ErrorStr = @ErrorStr + 'Unable to Write to File, Error Number: '+CONVERT(VARCHAR, @ErrorNo), @ErrorNo = 0
GOTO Error_Handler
END

SELECT @TEXTOUTPUT = 'using System.Data;'
EXECUTE @OLEResult = sp_OAMethod @FileID, 'WriteLine', NULL, @TEXTOUTPUT
SELECT @TEXTOUTPUT = 'using System.Configuration;'
EXECUTE @OLEResult = sp_OAMethod @FileID, 'WriteLine', NULL, @TEXTOUTPUT
SELECT @TEXTOUTPUT = 'using System.Web;'
EXECUTE @OLEResult = sp_OAMethod @FileID, 'WriteLine', NULL, @TEXTOUTPUT
SELECT @TEXTOUTPUT = 'using System.Web.Security;'
EXECUTE @OLEResult = sp_OAMethod @FileID, 'WriteLine', NULL, @TEXTOUTPUT
SELECT @TEXTOUTPUT = 'using System.Web.UI;'
EXECUTE @OLEResult = sp_OAMethod @FileID, 'WriteLine', NULL, @TEXTOUTPUT
SELECT @TEXTOUTPUT = 'using System.Web.UI.WebControls;'
EXECUTE @OLEResult = sp_OAMethod @FileID, 'WriteLine', NULL, @TEXTOUTPUT
SELECT @TEXTOUTPUT = 'using System.Web.UI.WebControls.WebParts;'
EXECUTE @OLEResult = sp_OAMethod @FileID, 'WriteLine', NULL, @TEXTOUTPUT
SELECT @TEXTOUTPUT = 'using System.Web.UI.HtmlControls;'
EXECUTE @OLEResult = sp_OAMethod @FileID, 'WriteLine', NULL, @TEXTOUTPUT
SELECT @TEXTOUTPUT = 'public class '+TableName FROM #ObjectInfo WHERE TableID = @Counter
EXECUTE @OLEResult = sp_OAMethod @FileID, 'WriteLine', NULL, @TEXTOUTPUT
SELECT @TEXTOUTPUT = '{'
EXECUTE @OLEResult = sp_OAMethod @FileID, 'WriteLine', NULL, @TEXTOUTPUT
--Column wise
SET @Count = 1
WHILE EXISTS(SELECT * FROM #ObjectColumns WHERE TableID = @Counter AND ColumnID = @Count)
BEGIN
SELECT @TEXTOUTPUT = 'private '+LTRIM(RTRIM(DataTypeName))+' _'+LTRIM(RTRIM(ColumnName))+';' FROM #ObjectColumns WHERE TableID = @Counter AND ColumnID = @Count
EXECUTE @OLEResult = sp_OAMethod @FileID, 'WriteLine', NULL, @TEXTOUTPUT
SELECT @TEXTOUTPUT = 'public '+LTRIM(RTRIM(DataTypeName))+' '+LTRIM(RTRIM(ColumnName)) FROM #ObjectColumns WHERE TableID = @Counter AND ColumnID = @Count
EXECUTE @OLEResult = sp_OAMethod @FileID, 'WriteLine', NULL, @TEXTOUTPUT
SELECT @TEXTOUTPUT = '{'
EXECUTE @OLEResult = sp_OAMethod @FileID, 'WriteLine', NULL, @TEXTOUTPUT
SELECT @TEXTOUTPUT = 'get'
EXECUTE @OLEResult = sp_OAMethod @FileID, 'WriteLine', NULL, @TEXTOUTPUT
SELECT @TEXTOUTPUT = '{'
EXECUTE @OLEResult = sp_OAMethod @FileID, 'WriteLine', NULL, @TEXTOUTPUT
SELECT @TEXTOUTPUT = 'return _'+LTRIM(RTRIM(ColumnName))+';' FROM #ObjectColumns WHERE TableID = @Counter AND ColumnID = @Count
EXECUTE @OLEResult = sp_OAMethod @FileID, 'WriteLine', NULL, @TEXTOUTPUT
SELECT @TEXTOUTPUT = '}'
EXECUTE @OLEResult = sp_OAMethod @FileID, 'WriteLine', NULL, @TEXTOUTPUT
SELECT @TEXTOUTPUT = 'set'
EXECUTE @OLEResult = sp_OAMethod @FileID, 'WriteLine', NULL, @TEXTOUTPUT
SELECT @TEXTOUTPUT = '{'
EXECUTE @OLEResult = sp_OAMethod @FileID, 'WriteLine', NULL, @TEXTOUTPUT
SELECT @TEXTOUTPUT = '_'+LTRIM(RTRIM(ColumnName))+' = Value;' FROM #ObjectColumns WHERE TableID = @Counter AND ColumnID = @Count
EXECUTE @OLEResult = sp_OAMethod @FileID, 'WriteLine', NULL, @TEXTOUTPUT
SELECT @TEXTOUTPUT = '}'
EXECUTE @OLEResult = sp_OAMethod @FileID, 'WriteLine', NULL, @TEXTOUTPUT
SELECT @TEXTOUTPUT = '}'
EXECUTE @OLEResult = sp_OAMethod @FileID, 'WriteLine', NULL, @TEXTOUTPUT
SET @Count = @Count + 1
END
SELECT @TEXTOUTPUT = 'public '+LTRIM(RTRIM(TableName))+'()' FROM #ObjectInfo WHERE TableID = @Counter
EXECUTE @OLEResult = sp_OAMethod @FileID, 'WriteLine', NULL, @TEXTOUTPUT
SELECT @TEXTOUTPUT = '{'
EXECUTE @OLEResult = sp_OAMethod @FileID, 'WriteLine', NULL, @TEXTOUTPUT
SELECT @TEXTOUTPUT = '}'
EXECUTE @OLEResult = sp_OAMethod @FileID, 'WriteLine', NULL, @TEXTOUTPUT
SELECT @TEXTOUTPUT = 'public '+LTRIM(RTRIM(TableName))+'(' FROM #ObjectInfo WHERE TableID = @Counter
EXECUTE @OLEResult = sp_OAMethod @FileID, 'Write', NULL, @TEXTOUTPUT
SET @Count = 1
WHILE EXISTS(SELECT * FROM #ObjectColumns WHERE TableID = @Counter AND ColumnID = @Count)
BEGIN
SELECT @TEXTOUTPUT = LTRIM(RTRIM(DataTypeName))+' '+LTRIM(RTRIM(ColumnName))+', ' FROM #ObjectColumns WHERE TableID = @Counter AND ColumnID = @Count
EXECUTE @OLEResult = sp_OAMethod @FileID, 'Write', NULL, @TEXTOUTPUT
SET @Count = @Count + 1
END
SELECT @TEXTOUTPUT = ')'
EXECUTE @OLEResult = sp_OAMethod @FileID, 'WriteLine', NULL, @TEXTOUTPUT
SELECT @TEXTOUTPUT = '{'
EXECUTE @OLEResult = sp_OAMethod @FileID, 'WriteLine', NULL, @TEXTOUTPUT
SET @Count = 1
WHILE EXISTS(SELECT * FROM #ObjectColumns WHERE TableID = @Counter AND ColumnID = @Count)
BEGIN
SELECT @TEXTOUTPUT = ' _'+LTRIM(RTRIM(ColumnName))+' = '+LTRIM(RTRIM(ColumnName))+';' FROM #ObjectColumns WHERE TableID = @Counter AND ColumnID = @Count
EXECUTE @OLEResult = sp_OAMethod @FileID, 'WriteLine', NULL, @TEXTOUTPUT
SET @Count = @Count + 1
END
SELECT @TEXTOUTPUT = '}'
EXECUTE @OLEResult = sp_OAMethod @FileID, 'WriteLine', NULL, @TEXTOUTPUT
SELECT @TEXTOUTPUT = '}'
EXECUTE @OLEResult = sp_OAMethod @FileID, 'WriteLine', NULL, @TEXTOUTPUT
EXECUTE @OLEResult = sp_OADestroy @FileID
EXECUTE @OLEResult = sp_OADestroy @FS
UPDATE #ObjectInfo SET isProcessed = 1 WHERE TableID = @Counter
IF NOT EXISTS(SELECT * FROM #ObjectInfo WHERE isProcessed = 0)
BEGIN
BREAK
END
END
DROP TABLE #ObjectInfo
DROP TABLE #ObjectColumns

Error_Handler:
IF ISNULL(@ErrorStr, '') <> ''
BEGIN
PRINT @ErrorStr
RETURN
END
END
--End of the SP
GO