locked
Script out - Create Table structure RRS feed

  • Question

  • Hi All,

    If i rightclick a table and script out, i ll get the create table query as shown below:

    Create Table Table_name

    (

    Column1 Datatype1,

    Column2 Datatype2

    )

    Question : Is there any way to get this Create table statement using T-SQL Query..? And the T-SQL query should be version independent..

    I have used Sys.Sql_modules to get the create procedure, create trigger statement e.t.c. But was not able to get the Create statement of Table..

    Please Help.


    Thursday, July 3, 2014 9:48 AM

Answers

All replies

  • DMO/SMO object library, but not directly by T-SQL

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    • Marked as answer by anand_j Monday, July 7, 2014 3:00 AM
    Thursday, July 3, 2014 9:51 AM
    Answerer
  • Hi,

    SMO objects will be version dependent right.. Thats y i m looking for an independent way sir..

    Thursday, July 3, 2014 1:53 PM
  • SMO can be used. I don't think it depends on the SQL version. I've generated script in most of the SQL Version(SQL 2000 onwards).

    If you want to do it via T-SQL there you might be some differences because of the change in catalog views.

    Quick browsing found some references below

    http://www.techrepublic.com/blog/the-enterprise-cloud/script-table-definitions-using-tsql/

    http://www.codeproject.com/Questions/305072/how-to-generate-sql-table-scripts-through-query-in

    --Prashanth


    Thursday, July 3, 2014 2:11 PM
  • This should get you started.

    You'll need to loop through the columns to get the guts of the create.

    SELECT c.name, t.name, s.name, st.name, c.precision, c.max_length,
    CASE 
      WHEN st.NAME IN ('float','image','text','uniqueidentifier','date','time','datetime2','datetimeoffset','tinyint','smallint','int','smalldatetime','real','money','datetime','smallmoney','bigint','bit','hierarchyid','timestamp','xml','geometry','geography','sql_variant','sysname') THEN st.name
      WHEN st.name IN ('decimal','numeric') THEN st.name + '(' + CONVERT(VARCHAR,c.precision) + ',' + CONVERT(VARCHAR,c.scale) + ')'
      WHEN st.name IN ('nvarchar','nchar','char','varbinary','varchar','binary','ntext') THEN st.name + '(' + CONVERT(VARCHAR,c.max_length) +')'
    END AS colText
      FROM sys.columns c
        INNER JOIN sys.tables t
    	  ON c.object_id = t.object_id
    	INNER JOIN sys.schemas s
    	  ON t.schema_id = s.schema_id
        INNER JOIN sys.systypes st
    	  ON c.system_type_id = st.xtype

    Thursday, July 3, 2014 2:38 PM
  • Hi Prashanth,

    Your current version of SMO will support old version of SQLServer, but not the future version of SQL Server..

    Friday, July 4, 2014 6:13 AM
  • Just wonder why not using  Generate Script Task where you can script out at once and everything, why would you want T-SQL?

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Friday, July 4, 2014 6:29 AM
    Answerer
  • hi,

    My Requirement is to connect to SQLServer from the frontend vb.net..
    So only if it is a query, i could be able to load the create table statement on the textbox in the form.

    Ok any way thanks a lot guys.. Hope there is no direct method other than SMO objects..

    Monday, July 7, 2014 3:00 AM