Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2003
    Location
    Mumbai
    Posts
    1

    Thumbs down Unanswered: Reverse Engineering

    Hi!

    I have problem of creating Table Scripts from already existing database. I am not able to find Not Null Constraint names and Default Constraints Names in any of System Tables/Views.

    I have used INFORMATION_SCHEMA for getting System information.
    Or please suggest me how i can recreate the database script.
    Sysobject, SysColumns ..etc System tables.

    Following code generates database scripts.

    CREATE Procedure TS_CreateAllTables_Raj
    AS
    begin
    declare @strsql varchar(8000)
    declare @v_constarint varchar(100)
    declare @datatype varchar(30)
    DECLARE @tablename varchar(100)
    DECLARE @table varchar(100)
    DECLARE @colName varchar(100)
    DECLARE @v_AcnBy numeric(3)
    DECLARE @tableBody varchar(1000)
    declare @constraintName varchar(100)
    declare @constraintType varchar(100)
    declare @constraintBody varchar(200)
    declare @field varchar(100)
    declare @NotNull varchar(100)
    --Declare cursor to fetch all tables
    DECLARE tnames_cursor CURSOR
    FOR
    SELECT TABLE_NAME,column_name,data_type,constraint_body
    FROM
    HT_DBSCRIPT WHERE TABLE_NAME LIKE 'TS_%' ORDER BY TABLE_NAME
    OPEN tnames_cursor

    FETCH NEXT FROM tnames_cursor INTO @tablename,@colName,@dataType,@constraintBody

    print 'CREATE TABLE ' + @tableName + '('

    WHILE (@@FETCH_STATUS = 0)

    BEGIN

    IF @table <> @tableName
    BEGIN
    print 'CREATE TABLE ' + @tableName
    SELECT @field=table_short_name FROM HT_TABLE_SHRT_NAME
    WHERE TABLE_NAME=@tableName
    print '('
    END
    IF LEN(@datatype)> 0--print all columns with datatypes
    BEGIN
    IF LEN(@field) > 0 --table has acn column .then take field name prefix
    BEGIN
    IF @constraintBody='NO'
    BEGIN
    PRINT @colName + ' ' + @dataType
    select @NotNull=substring(@colname,
    len(@field),
    len(@colName))
    PRINT 'Constraint ' + @field + 'NN' + @NotNull + ' NOT NULL,'
    END
    ELSE
    BEGIN
    PRINT @colName + ' ' + @dataType + ','
    END
    END
    ELSE
    BEGIN
    PRINT @colName + ' ' + @dataType + ','
    END
    END
    IF LEN(@datatype) = 0--print all constraints
    BEGIN
    PRINT @constraintBody + ','
    END

    FETCH NEXT FROM tnames_cursor INTO @tablename,@colName,@dataType,@constraintBody

    IF @table <> @tableName
    BEGIN
    PRINT ')'
    PRINT 'GO'
    PRINT ''
    PRINT 'CREATE TABLE ' + @tableName
    PRINT '('
    END
    SELECT @field=table_short_name FROM HT_TABLE_SHRT_NAME WHERE TABLE_NAME=@tableName
    SET @table=@tableName
    END
    print ')'
    CLOSE tnames_cursor
    DEALLOCATE tnames_cursor

    END


    thaks in advance
    Regards
    Raj

  2. #2
    Join Date
    Apr 2003
    Location
    Edison, NJ / Oakland, NJ (Work)
    Posts
    32

    mySQL doesn't support stored procedures by default

    -----------------------------------
    Smith Hayward
    -----------------------------------

  3. #3
    Join Date
    Jan 2003
    Location
    Duncan BC Canada
    Posts
    80
    Your code looks a lot like T-SQL (MS SQL Server). Are you sure you're in the right forum?
    Bradley

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •