-2

In MySQL you can do SELECT CREATE TABLE .... and it will return the CREATE statement that was used to create this table. I need to do the same in SQL Server.

Is there any similar functionality in SQL Server? I have a table name test_table.

I need to run a SELECT statement that would return the CREATE TABLE string that was used to create this table. I tried this but it didn't work. How can I achieve this result in SQL Server?

Dale K
  • 25,246
  • 15
  • 42
  • 71
john
  • 647
  • 5
  • 23
  • 53
  • 4
    short answer - there is no equivalent. – SMor Aug 07 '21 at 01:44
  • 1
    Does this answer your question? [Generate SQL Create Scripts for existing tables with Query](https://stackoverflow.com/questions/706664/generate-sql-create-scripts-for-existing-tables-with-query) – Stu Aug 07 '21 at 08:47
  • For MySQL it's `SHOW CREATE TABLE` and **not** `SELECT CREATE TABLE` – LanX Apr 07 '23 at 00:24

1 Answers1

0

You could try SELECT INTO and specify the name of a temporary table (prefixed with #). It also works with physical tables (although I've not found a use for this). Something like this

select '123' as colName into #newTable;

select * from #newTable;

colName
123
SteveC
  • 5,955
  • 2
  • 11
  • 24
  • thank you. how does this help generate the CREATE statement? Should I use `select create test_table as colName into #newTable` ? – john Aug 07 '21 at 01:11
  • You could use `select 123 as colName into test_table` and it would create the table – SteveC Aug 07 '21 at 01:39
  • From SQL Server Management Studio, can you view Object Explorer Details, then select all of the tables you want, right click and generate Create Table scripts to new query editor window, or file. – David.Warwick Aug 07 '21 at 02:18
  • I found [this link](https://www.mssqltips.com/sqlservertip/5699/auto-generate-create-table-script-based-on-sql-server-query/) which might be interesting. – SteveC Aug 07 '21 at 02:30