How to get table script from t-sql ?

All Replies

• Friday, January 18, 2013 9:03 AM

   

   
  

  0

  Within SS management studio, Right click the table/view/SP > Script table as > Drop and create to > New query window

  then you will get the whole script to recreate the DB object.

  Or by T-sql, try this

  SELECT a.[name] as 'Table',
    b.[name] as 'Column',
    c.[name] as 'Datatype',
    b.[length] as 'Length',
    CASE
     WHEN b.[cdefault] > 0 THEN d.[text]
     ELSE NULL
    END as 'Default',
    CASE
     WHEN b.[isnullable] = 0 THEN 'No'
     ELSE 'Yes'
    END as 'Nullable'
  FROM  sysobjects a
  INNER JOIN syscolumns b
  ON  a.[id] = b.[id]
  INNER JOIN systypes c
  ON  b.[xtype] = c.[xtype]
  LEFT JOIN syscomments d
  ON  b.[cdefault] = d.[id]
  WHERE a.[xtype] = 'u'
  -- 'u' for user tables, 'v' for views.
  --and a.[name]='table name'
  AND  a.[name] <> 'dtproperties'
  ORDER BY a.[name],b.[colorder]
  

  ---

  Many Thanks & Best Regards, Hua Min

  
  
  

  
  

  • Edited by HuaMin ChenMicrosoft Community Contributor Friday, January 18, 2013 9:14 AM
  • Marked As Answer by Kalman TothMicrosoft Community Contributor, Moderator Monday, January 28, 2013 10:12 PM
  •  
  • Reply
  • Quote

   
• Friday, January 18, 2013 9:22 AM

   

   
  

  0

  Within SS management studio, Right click the table/view/SP > Script table as > Drop and create to > New query window

  then you will get the whole script to recreate the DB object.

  Or by T-sql, try this

  SELECT a.[name] as 'Table',
    b.[name] as 'Column',
    c.[name] as 'Datatype',
    b.[length] as 'Length',
    CASE
     WHEN b.[cdefault] > 0 THEN d.[text]
     ELSE NULL
    END as 'Default',
    CASE
     WHEN b.[isnullable] = 0 THEN 'No'
     ELSE 'Yes'
    END as 'Nullable'
  FROM  sysobjects a
  INNER JOIN syscolumns b
  ON  a.[id] = b.[id]
  INNER JOIN systypes c
  ON  b.[xtype] = c.[xtype]
  LEFT JOIN syscomments d
  ON  b.[cdefault] = d.[id]
  WHERE a.[xtype] = 'u'
  -- 'u' for user tables, 'v' for views.
  --and a.[name]='table name'
  AND  a.[name] <> 'dtproperties'
  ORDER BY a.[name],b.[colorder]

  useless for creating script.

  Does anyone have script ?

  ---

  -- Bhavesh Thakkar
  
  Sr.Database Administrator
  My Blog
  Linked In Profile

  • Reply
  • Quote

   
• Friday, January 18, 2013 4:40 PM

   

   
  

  0

  Hi,

  please see the below thread of "SQL Server Express "

  http://social.msdn.microsoft.com/Forums/en-US/sqlexpress/thread/715ccb74-f215-4242-9ce2-0c1563e67ac8/

  ---

  Ahsan Kabir Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread. http://www.aktechforum.blogspot.com/

  • Marked As Answer by Kalman TothMicrosoft Community Contributor, Moderator Monday, January 28, 2013 10:13 PM
  •  
  • Reply
  • Quote