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

User · Answer

sp help  YourTableName

User · Answer

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

User · Answer

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

User · Answer

The easiest and quickest way I can think of would be to use sp help  sp help  TableName

User · Answer

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     lt server gt    database     lt database gt    table     lt table gt    schema     lt schema gt     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     lt server gt    user     lt user gt    password     lt pasword gt    database     lt database gt    table     lt table gt    schema     lt schema gt     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

User · Answer

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  lt server gt                             lt user gt   lt password gt   lt database gt   lt schema gt   lt table gt                                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 lt string gt                     Select s   gt  s     n     GO                    ToList                    ForEach Console WriteLine

User · Answer

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

User · Answer

Try the sp help stored procedure   sp help  lt

User · Answer

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

User · Answer

SELECT ORDINAL POSITION  COLUMN NAME  DATA TYPE  CHARACTER MAXIMUM LENGTH          IS NULLABLE FROM INFORMATION SCHEMA COLUMNS WHERE TABLE NAME    EMPLOYEES

User · Answer

A variation of  Anthony Faull s answer for those using LINQPad   new Server new ServerConnection this Connection DataSource        Databases this Connection Database       Tables   lt table gt     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

User · Answer

Have you tried sp help   sp help  TableName

User · Answer

Since SQL 2012 you can run the following statement   Exec sp describe first result set  tsql  N Select   from  lt yourtable gt     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   docs microsoft com en-us sql relational-databases system-stored-procedures sp-describe-first-result-set-transact-sql

User · Answer

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

User · Answer

Another way is to execute sp columns procedure   EXEC sys sp columns  TABLE NAME    YourTableName

[sql-server] T-SQL query to show table definition?

Examples related to sql-server

Examples related to tsql