T-SQL query to show table definition?

Question

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')

Have you tried sp_help?

sp_help 'TableName'

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 ([email protected]).

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

sp_help 'TableName'

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);
        }
    }
}