Answered by:
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.
Thursday, July 3, 2014 9:48 AM
Answers
-
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 AMAnswerer
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 AMAnswerer -
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
- Edited by Prashanth Jayaram Thursday, July 3, 2014 2:13 PM
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 AMAnswerer -
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