Script out - Create Table structure

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.

Answer 1

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

Answer 2

Hi,

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

Answer 3

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

Answer 4

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

Answer 5

Hi Prashanth,

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

Answer 6

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

Answer 7

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..