CODEWITHSUNDEEP
t-sqlsql-server-2012-express
wonea
wonea

Reputation: 4969

How to get Object Schema using TSQL

Aside from sp_help 'mytablename' which gives a break of each element of an object, is there a way getting the raw SQL. Just the same as doing the following in Microsoft SQL Server.

Microsoft SQL Server - Script Table

Using sp_helptext 'mytablename' says the following;

There is no text for object 'Track'.

Update: sp_helptext works for a stored procedure but not for a table;

sp_help on a Stored Procedure

Upvotes: 1

Views: 404

Answers (1)

Rahul
Rahul

Reputation: 77876

use sp_helptext stored procedure to get the same; like

sp_helptext tablename

This will return you the SQL CREATE TABLE text for the specific table (or whichever object you specify; could be stored procedure\function\etc ).

Per your edited post where you say There is no text for object 'Track'. I am not sure but check whether you have permission to see object body (OR) it's not encrypted with WITH ENCRYPTION option.

you can as well use OBJECT_DEFINITION like OBJECT_DEFINITION ( object_id ).

SELECT OBJECT_DEFINITION (OBJECT_ID(N'Person.uAddress'))

Only difference is, it won't get you the source code for a Table (all objects other than table)

EDIT:

You can script a table using SSMS (same way you have pictured in your post). Not sure why that's not serving your purpose.

You can as well use sp_help.

If you are looking for something like DESCRIBE in Oracle (OR) SHOW TABLE in MySQL then there is no such built-in mechanism available but you can combine all the data in INFORMATION_SCHEMA.COLUMNS/sys.objects/etc and create a custom script to achieve the same. Found the below post where people have already documented the same kind of script. You can use the same. Take a look.

How can I show the table structure in SQL Server query?

In SQL Server, how do I generate a CREATE TABLE statement for a given table?

Upvotes: 2

Related Questions