T-SQL query to show table definition?

Question

What is a query that will show me the full definition, including indexes and keys for a SQL Server table? I want a pure query - and know that SQL Studio can give this to me, but I am often on "wild" computers that have only the most bare-bones apps and I have no rights to install studio. But SQLCMD is always an option.

UPDATE: I have tried sp_help, but is just yields one record which shows Name, Owner, Type and Created_Datetime. Is there something else I am missing with sp_help?

Here is what I call:

sp_help airports

Note that I really do want the DDL that defines the table.

Answer 1

There is no easy way to return the DDL. However you can get most of the details from Information Schema Views and System Views.

SELECT ORDINAL_POSITION, COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH
       , IS_NULLABLE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Customers'

SELECT CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE
WHERE TABLE_NAME = 'Customers'

SELECT name, type_desc, is_unique, is_primary_key
FROM sys.indexes
WHERE [object_id] = OBJECT_ID('dbo.Customers')

Answer 2

Have you tried sp_help?

sp_help 'TableName'

Answer 3

Visit http://www.stormrage.com/SQLStuff/sp_GetDDL_Latest.txt.

You will find the code of sp_getddl procedure for SQL Server. The purpose of the procedure is script any table, temp table or object.

USAGE:

exec sp_GetDDL GMACT

or

exec sp_GetDDL 'bob.example'

or

exec sp_GetDDL '[schemaname].[tablename]'

or

exec sp_GetDDL #temp

I tested it on SQL Server 2012, and it does an excellent job.

I'm not the author of the procedure. Any improvement you make to it send to Lowell Izaguirre (scripts@stormrage.com).

Answer 4

The easiest and quickest way I can think of would be to use sp_help

sp_help 'TableName'

Answer 5

Use this little Windows command-line app that gets the CREATE TABLE script (with constraints) for any table. I've written it in C#. Just compile it and carry it on a memory stick. Perhaps someone can port it to Powershell.

using System;
using System.Linq;
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Smo;
namespace ViewSource
{
    public class ViewSource
    {
        public static void Main(string[] args)
        {
            if (args.Length != 6)
            {
                Console.Error.WriteLine("Syntax: ViewSource.exe <server>" +
                     " <user> <password> <database> <schema> <table>");
            }

            Script(args[0], args[1], args[2], args[3], args[4], args[5]);
        }
        private static void Script(string server, string user,
            string password, string database, string schema, string table)
        {
            new Server(new ServerConnection(server, user, password))
                .Databases[database]
                .Tables[table, schema]
                .Script(new ScriptingOptions { SchemaQualify = true,
                                               DriAll = true })
                .Cast<string>()
                .Select(s => s + "\n" + "GO")
                .ToList()
                .ForEach(Console.WriteLine);
        }
    }
}

Answer 6

Since SQL 2012 you can run the following statement:

Exec sp_describe_first_result_set @tsql= N'Select * from <yourtable>'

If you enter a complex select statement (joins, subselects, etc), it will give you the definition of the result set. This is very handy, if you need to create a new table (or temp table) and you don't want to check every single field definition manually.

https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-describe-first-result-set-transact-sql

Answer 7

sp_help 'YourTableName'

Answer 8

I know it's an old question, but exactly what I was looking for. Because I want to batch script some tables, I rewrote the C# code from Anthony Faull for PowerShell.

This one is uses Integrated Security:

Import-Module sqlps

$serverInstance = "<server>"
$database = "<database>"
$table = "<table>"
$schema = "<schema>"

$options = New-Object -TypeName Microsoft.SqlServer.Management.Smo.ScriptingOptions
$options.DriAll = $true
$options.SchemaQualify = $true

$connection = New-Object -TypeName Microsoft.SqlServer.Management.Common.ServerConnection `
    -ArgumentList $serverInstance
$server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server `
    -ArgumentList $connection

$server.Databases.Item($database).Tables.Item($table, $schema).Script($options) `
    | ForEach-Object -Process { $_ + "`nGO"}

And here with username and password:

Import-Module sqlps

$serverInstance = "<server>"
$user = "<user>"
$password = "<pasword>"
$database = "<database>"
$table = "<table>"
$schema = "<schema>"

$options = New-Object -TypeName Microsoft.SqlServer.Management.Smo.ScriptingOptions
$options.DriAll = $true
$options.SchemaQualify = $true

$connection = New-Object -TypeName Microsoft.SqlServer.Management.Common.ServerConnection `
    -ArgumentList $serverInstance
$connection.LoginSecure = $false
$connection.Login = $user
$connection.Password = $password
$server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server `
    -ArgumentList $connection

$server.Databases.Item($database).Tables.Item($table, $schema).Script($options) `
    | ForEach-Object -Process { $_ + "`nGO"}

Answer 9

This will return columns, datatypes, and indexes defined on the table:

--List all tables in DB
select * from sysobjects where xtype = 'U'

--Table Definition
sp_help TableName

This will return triggers defined on the table:

--Triggers in SQL Table
select * from sys.triggers where parent_id = object_id(N'SQLTableName') 

Answer 10

Simply type the table name and select it and press ATL + F1

Say your table name is Customer then open a new query window, type and select the table name and press ALT + F1

It will show the complete definition of table.

Answer 11

A variation of @Anthony Faull's answer for those using LINQPad:

new Server(new ServerConnection(this.Connection.DataSource))
    .Databases[this.Connection.Database]
    .Tables["<table>", "dbo"]
    ?.Script(new ScriptingOptions {
        SchemaQualify = true,
        DriAll = true,
    })

You'll need to reference 2 assemblies:

• Microsoft.SqlServer.ConnectionInfo.dll
• Microsoft.SqlServer.Smo.dll

And add namespace references as mentioned in Anthony's snippet.

Answer 12

SELECT ORDINAL_POSITION, COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH
       , IS_NULLABLE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'EMPLOYEES'

Answer 13

Try the sp_help stored procedure.

sp_help <>

Answer 14

Another way is to execute sp_columns procedure.

EXEC sys.sp_columns @TABLE_NAME = 'YourTableName'

Answer 15

As an addition to Barry's answer. The sp_help can also be used by itself to iterate all of the objects in a particular database. You also have sp_helptext for your arsenal, which scripts out programmatic elements, like stored procedures.

Answer 16

General table definition is shown using stored procedure sp_help, as said before:

sp_help 'table_name_in_current_db_context'

When using tables of multiple contexts is useful to prefix the command above with the desired context, instead of changing them with use db_xyz:

DB_Products..sp_help 'MyTable' -- for definition of MyTable in DB_Products

And it works with temp tables also:

tempdb..sp_help '#TempTable' -- for definition of #TempTable in current context.

Answer 17

There is an easy way to get DDL scripts for any database object.

1. Open SQL Server Management Studio
2. Connect to the source database server.
3. Expand the databases tree.
4. Right-click on the database with the tables to export.
5. In the sub-menu, expand "Tasks".
6. In the sub-menu, choose "Generate Scripts..."
7. Use the wizard to choose the objects to export.
8. For the scripting options, choose 1 script per object.

This will export a file for every table, view, stored proc, user, role, or schema you chose. Take that to the destination computer.