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.
This question is related to
sql-server
tsql
Another way is to execute sp_columns procedure.
EXEC sys.sp_columns @TABLE_NAME = 'YourTableName'
The easiest and quickest way I can think of would be to use sp_help
sp_help 'TableName'
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:
And add namespace references as mentioned in Anthony's snippet.
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.
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]).
sp_help 'YourTableName'
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);
}
}
}
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.
Have you tried sp_help?
sp_help 'TableName'
SELECT ORDINAL_POSITION, COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH
, IS_NULLABLE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'EMPLOYEES'
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')
Try the sp_help stored procedure.
sp_help <>
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"}
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.
Source: Stackoverflow.com