InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 18 May 2013 18:11:19 GMT20http://www.sqlservercentral.com/Forums/Topic403726-601-1.aspxPretty awesome stuff! I like it when I can find things to keep my developers happy, and this is just one of a bounty of goodies I have been collecting for them.Thank you for the revision, and to the original author as well!Thu, 03 Mar 2011 11:11:47 GMTSQL_ME_RICHhttp://www.sqlservercentral.com/Forums/Topic403726-601-1.aspxMadhu, I did not create the original stored procedure, I just made some corrections.It was created by Satyanarayana Bommidi, he(?) deserves the credit.BillWed, 14 Nov 2007 07:13:28 GMTWilliam Sorannohttp://www.sqlservercentral.com/Forums/Topic403726-601-1.aspxThanks Williams,I figured out whats wrong. I have created the folder in my PC rather than the Server's C Drive. Once I created and ran the Stored Proc, it created the files clean.Satyanaraya garu,Very thanks for developing such a nice functionality. I'm beel looking for such functionality. I had used MyGeneration Doodads before for the same functionality. But this one pretty straight forward and optimal compared to that..MadhuTue, 13 Nov 2007 18:32:37 GMTMadhu-453926http://www.sqlservercentral.com/Forums/Topic403726-601-1.aspxMadhu, I had the same thing happen to me. It stopped once I created the path on my system that the sp is looking for.Make sure that c:\SQLClasses folder exists.Change the IF so it has the following:[quote]IF @OLEResult &lt;&gt; 0 PRINT 'OpenTextFile ' + @FileName + ' error number:' + CAST(@OLEResult AS VARCHAR(20))[/quote]if you get the following:OpenTextFile c:\sqlclasses\tablen.cs error number:-2146828212This error number means the path is not found.Tue, 13 Nov 2007 16:47:56 GMTWilliam Sorannohttp://www.sqlservercentral.com/Forums/Topic403726-601-1.aspxWilliam,I'm still getting the ourput as OpenTextFile C:\SQLClasses\table1.csOpenTextFile C:\SQLClasses\table2.cs....OpenTextFile C:\SQLClasses\tableN.cs[b][quote]EXECUTE @OLEResult = sp_OAMethod @FS, 'OpenTextFile', @FileID OUT, @FileName, 8, 1IF @OLEResult &lt;&gt; 0 PRINT 'OpenTextFile ' + @FileName[/quote][/b]I believe it is because of the above statement. And I do not know how to fix it.Tue, 13 Nov 2007 16:10:49 GMTMadhu-453926http://www.sqlservercentral.com/Forums/Topic403726-601-1.aspxIf you used the original script to create the sp, it has errors.Copy the script from my post that has the corrected version.The script is an ALTER statement. You should be able to run it from a new query that is connected to the db you created the sp in.Tue, 13 Nov 2007 16:02:52 GMTWilliam Sorannohttp://www.sqlservercentral.com/Forums/Topic403726-601-1.aspxI'm getting output as (x row(s ) affected) - x is a numberOpenTextFile (a whole bunch of them)And the C:\SQLClasses directory is empty.Do I need to set any Permissions to sp_OACreate or other stored procedure?I don't see it as problem as there no error message regarding permissions.MadhuTue, 13 Nov 2007 15:49:30 GMTMadhu-453926http://www.sqlservercentral.com/Forums/Topic403726-601-1.aspxThe procedure works great, once you make some minor changes.Please see the corrected code below:SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO/*Purpose : This SP will be create C# classes using all table in the specified DatabaseAuthor : Satyanarayana BommidiCreated Date : 16th July 2004Example : EXEC uspSQLObjectsToClass 'Northwind'Output folder : c:\SQLClassesPlease see additional comments below. William Soranno 11/13/2007*/ALTER PROCEDURE [dbo].[uspSQLObjectsToClass] (@DBName VARCHAR(100))ASBEGIN 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) &gt; 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 &lt;&gt; ''dtproperties'' ORDER BY name') SET @ErrorNo = @@Error IF ISNULL(@ErrorNo, 0) &gt; 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-- Had to put this into an EXEC because SYSCOLUMNS needs-- to be database qualified. EXEC ('INSERT INTO #ObjectColumns(TableID, ColumnID, ColumnName, DataTypeName) SELECT id, colid, col.name, typ.name FROM ['+@DBName+'].dbo.SYSCOLUMNS col INNER JOIN SYSTYPES typ on col.xtype = typ.xtype INNER JOIN #ObjectInfo obj on col.id = obj.TableID where typ.name &lt;&gt; ''sysname'' order by 1, 2') SET @ErrorNo = @@Error IF ISNULL(@ErrorNo, 0) &gt; 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 -- Make sure the folder exists. SET @FileName = 'c:\SQLClasses\'+LTRIM(RTRIM(@FileName))+'.cs' --Deleting the file EXECUTE @OLEResult = master..xp_fileexist @FileName, @FS OUT IF @FS &gt; 0 EXECUTE ('EXEC master..xp_CMDShell "Del '+@FileName+'"') SET @ErrorNo = @@Error IF ISNULL(@ErrorNo, 0) &gt; 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) &gt; 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 &lt;&gt; 0 PRINT 'OpenTextFile ' + @FileName SET @ErrorNo = @@Error IF ISNULL(@ErrorNo, 0) &gt; 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) &gt; 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, '') &lt;&gt; '' BEGIN PRINT @ErrorStr RETURN END END--End of the SPTue, 13 Nov 2007 13:04:44 GMTWilliam Sorannohttp://www.sqlservercentral.com/Forums/Topic403726-601-1.aspxComments posted to this topic are about the item [B]<A HREF="/scripts/Miscellaneous/31997/">This SP will be create C# classes using all table </A>[/B]Thu, 27 Sep 2007 14:27:03 GMTSatyanarayana B