Results 1 to 3 of 3
  1. #1
    Join Date
    May 2004
    Posts
    144

    Unanswered: produce SQL statement for a database

    hi
    is there any feature in MSSql that produce SQL statement for a database(include CREATE TABLE, INSERT Records, ...)

  2. #2
    Join Date
    Apr 2004
    Posts
    1
    (for SQL Server 2000...and maybe 7.0)

    In the SQL Query Analyzer Object Browser, drill down to your table, right-click on the table name and select "Script Object...As" and it will generate the SQL for you.

    Additionally, in Enterprise Manager, drill down to your "Tables" and select the table you want to query. Right-click on the table, Select Open Table --> Query. You SQL statement is generated dynamically as you select fields. A Select statement is the default. In the gray area where you see the table and all of it's fields, Right-click and there is a selection for "Change Type." In there you can change the query to an Insert, Create, etc...

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I prefer to build my own..

    Code:
    USE NorthWind
    
    DECLARE @TBName sysname, @SQL varchar(8000)
    
    
    SELECT @TBName = 'Cust', @SQL = ''
    
    SELECT @SQL = @SQL + RTRIM(SQL) FROM (
    --SELECT SQL FROM (
    SELECT RTRIM(' SELECT  ' + RTRIM(COLUMN_NAME)) As SQL, TABLE_NAME, 3 As SQL_Group, ORDINAL_POSITION As Row_Order
      FROM INFORMATION_SCHEMA.COLUMNS
     WHERE TABLE_NAME LIKE @TBName+'%'
       AND ORDINAL_POSITION = 1
    UNION ALL
    SELECT RTRIM(', ' + RTRIM(COLUMN_NAME)) As SQL, TABLE_NAME, 3 As SQL_Group, ORDINAL_POSITION As Row_Order
      FROM INFORMATION_SCHEMA.COLUMNS
     WHERE TABLE_NAME LIKE @TBName+'%'
       AND ORDINAL_POSITION <> 1
    UNION ALL
    SELECT RTRIM(' FROM [' + RTRIM(TABLE_NAME) + ']') As SQL, TABLE_NAME, 4 As SQL_Group, 1 As Row_Order
      FROM INFORMATION_SCHEMA.COLUMNS
     WHERE TABLE_NAME LIKE @TBName+'%'
       AND ORDINAL_POSITION = 1
    UNION ALL
    SELECT RTRIM(' GO ') As SQL, TABLE_NAME, 5 As SQL_Group, 1 As Row_Order
      FROM INFORMATION_SCHEMA.COLUMNS
     WHERE TABLE_NAME LIKE @TBName+'%'
       AND ORDINAL_POSITION = 1
    ) AS XXX
    Order By TABLE_NAME, SQL_Group, Row_Order
    
    SELECT @SQL
    
    --EXEC(@SQL)
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

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