Is there an easy way to get the scripts of a table with only t-sql, not with the wizard?

  • Hi I am trying to build something that needs to be very dynamic that mimic a replication, for that I will need to get the scripts of some of my tables, index included as to create the tables on another repository and apply changes, is there a way to get those scripts without the wizard in an easy way?

    I can build the table getting info from sp_help and the indexes from other sources but it is a bit complex and I was looking for something more simple

    Thanks!

  • See if this helps:

    SELECT

    DB_NAME() AS DatabaseName, T.[name] AS Table_Name, AC.[name] AS Column_Name,

    AC.column_id AS Ordinal_Position, TY.[name] AS Data_type,

    CASE WHEN AC.[precision] = 0 THEN AC.max_length ELSE AC.[precision] END AS CLength,

    AC.is_nullable AS IsNullable,

    OBJECT_DEFINITION(AC.default_object_id) AS Column_Default

    FROM sys.tables AS T

    INNER JOIN sys.all_columns AC ON T.[object_id] = AC.[object_id]

    INNER JOIN sys.types TY ON AC.[system_type_id] = TY.[system_type_id] AND AC.[user_type_id] = TY.[user_type_id]

    WHERE T.[is_ms_shipped] = 0

    ORDER BY 1, 2, 4;

  • I believe that it's easier to use INFORMATION_SCHEMA views.

    SELECT TABLE_SCHEMA,

    TABLE_NAME,

    COLUMN_NAME,

    DATA_TYPE,

    CASE WHEN DATA_TYPE IN( 'char', 'varchar', 'nchar', 'nvarchar') THEN '(' + CAST( CHARACTER_MAXIMUM_LENGTH AS varchar(10)) + ')'

    WHEN DATA_TYPE IN('decimal', 'numeric') THEN '(' + CAST( NUMERIC_PRECISION AS varchar(10)) + ',' + CAST( NUMERIC_SCALE AS varchar(10)) + ')'

    ELSE '' END,

    CASE WHEN IS_NULLABLE = 'YES' THEN 'NULL' ELSE 'NOT NULL' END,

    CASE WHEN DATA_TYPE IN( 'char', 'varchar', 'nchar', 'nvarchar') THEN COLLATION_NAME ELSE '' END

    FROM INFORMATION_SCHEMA.COLUMNS

    You could add more details if needed.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • That was my point, but I didn't have an example ready. 😀

    I'm making the link clickable to make it easier for others.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Thanks everyone, really good stuff, I will need to add a few things, will post the results back when I am done

  • yep it's possible, but it's not built in.

    using the GUI or via SMO is infinitely easier; I know, because i poured weeks of effort down the TSQL-only method, and it's probably only 95% completed.

    i made a script i contributed,and an article that gets a little bit of traffic as well; the current version scripts out any table, temp table, or procedure/function/view.

    exec sp_GetDDLa TableName;

    exec sp_GetDDLa #TempTable;

    exec sp_GetDDLa ProcedureName;

    see the thread for a lot of different versions from other people who took my version and adapted it to their own needs.

    sp_GetDDLa_Latest.txt

    it's like a thousand lines of code, and can still be improved when it comes to scripting the indexes details out.

    the thread:

    http://www.sqlservercentral.com/Forums/Topic751783-566-7.aspx

    and the four year old article from 2009:

    Get DDL for any SQL 2005 table[/url]

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply