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 @Counter	INT,		--loop counter
		@Count		INT,		--loop counter
		@ObjectName	VARCHAR(100),	--to hold Object Name like (Tables, Views, SPs and Triggers)
		@ErrorNo	INT,		--to hold the error number
		@ErrorStr	VARCHAR(255),	--to hold the error description
		@TEXTOUTPUT	VARCHAR(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