How to Describe Table in SQL Server 2008

How to describe table in SQL Server 2008?

According to this documentation:

DESC MY_TABLE

is equivalent to

SELECT column_name "Name", nullable "Null?",
concat(concat(concat(data_type,'('),data_length),')') "Type" FROM
user_tab_columns WHERE table_name='TABLE_NAME_TO_DESCRIBE';

I've roughly translated that to the SQL Server equivalent for you - just make sure you're running it on the EX database.

SELECT column_name AS [name],
       IS_NULLABLE AS [null?],
       DATA_TYPE + COALESCE('(' + CASE WHEN CHARACTER_MAXIMUM_LENGTH = -1
                                  THEN 'Max'
                                  ELSE CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR(5))
                                  END + ')', '') AS [type]
FROM   INFORMATION_SCHEMA.Columns
WHERE  table_name = 'EMP_MAST'

Describe table structure

sp_help tablename in sql server -- sp_help [ [ @objname = ] 'name' ]

desc tablename in oracle -- DESCRIBE { table-Name | view-Name }

How to Describe table in SQL Server 2014

In SSMS find table you need, right-click on it, choose Script Table As... -> CREATE To -> New Query Editor Window

That will give you creation script where you can see all details about table structure.

Another way is to use system tables:

USE MyDB

SELECT  c.name,
        s.name,
        c.max_length,
        c.is_nullable
FROM sys.columns c
LEFT JOIN sys.systypes s
    ON s.xusertype= c.system_type_id
WHERE object_id = object_id(N'Employees')

This will give you:

name            name        max_length  is_nullable
EmployeeID      int         4           0
LastName        nvarchar    40          0
FirstName       nvarchar    20          0
...etc

Or views:

USE MyDB

SELECT  TABLE_NAME, 
        COLUMN_NAME, 
        DATA_TYPE,
        IS_NULLABLE 
FROM INFORMATION_SCHEMA.COLUMNS  
WHERE TABLE_NAME = 'Employees';  

Output:

TABLE_NAME  COLUMN_NAME DATA_TYPE   IS_NULLABLE
Employees   EmployeeID  int         NO
Employees   LastName    nvarchar    NO
Employees   FirstName   nvarchar    NO
Employees   Title       nvarchar    YES

Note: also you can use EXEC sp_help 'Employees' more about this SP you can read on MSDN.

How I can get table definition in SQL SERVER 2008 R2 using SQL query?

Have a look on

SELECT * FROM INFORMATION_SCHEMA.TABLES

and

SELECT * FROM INFORMATION_SCHEMA.COLUMNS

desc table in SQL Server?

dont use schema dbo.

exec sp_columns [ATRESMEDIA Resource Time Registr_];

why? because, following are the parameters accepted by sp_columns stored proc:

sp_columns [ @table_name = ] object

[ , [ @table_owner = ] owner ]

[ , [ @table_qualifier = ] qualifier ]

[ , [ @column_name = ] column ]

[ , [ @ODBCVer = ] ODBCVer ]

source: msdn

update:
Martin's explanation as in comment:

Strings in SQL Server are delimited by single quotes - as a parameter to a stored proc in very limited circumstances it will allow you to skip the quotes but the dot breaks that. exec sp_columns 'dbo.[ATRESMEDIA Resource Time Registr_]'; wouldn't give the syntax error - but that wouldn't be what the proc expects anyway as the schema would need to be the second param

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

For SQL Server, if using a newer version, you can use

select *
from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME='tableName'

There are different ways to get the schema. Using ADO.NET, you can use the schema methods. Use the DbConnection's GetSchema method or the DataReader'sGetSchemaTable method.

Provided that you have a reader for the for the query, you can do something like this:

using(DbCommand cmd = ...)
using(var reader = cmd.ExecuteReader())
{
    var schema = reader.GetSchemaTable();
    foreach(DataRow row in schema.Rows)
    {
        Debug.WriteLine(row["ColumnName"] + " - " + row["DataTypeName"])
    }
}

See this article for further details.

SQL comments on create table on SQL Server 2008

You can put comments on both tables and columns by creating what are called Extended Properties. You can put extended properties at both the table level and column level. This can be done via T-SQL or SSMS.

For example, in T-SQL it looks something like this:

sp_addextendedproperty 'BackColor', 'Red', 'user', '<schema name>', 'table', '<table name', 'column', '<column name>'.

You can read more about it on sp_addextendedproperty (Transact-SQL).

How to get a view table query (code) in SQL Server 2008 Management Studio

In Management Studio, open the Object Explorer.

• Go to your database
• There's a subnode Views
• Find your view
• Choose Script view as > Create To > New query window

and you're done!

If you want to retrieve the SQL statement that defines the view from T-SQL code, use this:

SELECT  
    m.definition    
FROM sys.views v
INNER JOIN sys.sql_modules m ON m.object_id = v.object_id
WHERE name = 'Example_1'

Find the last time table was updated

If you're talking about last time the table was updated in terms of its structured has changed (new column added, column changed etc.) - use this query:

SELECT name, [modify_date] FROM sys.tables

If you're talking about DML operations (insert, update, delete), then you either need to persist what that DMV gives you on a regular basis, or you need to create triggers on all tables to record that "last modified" date - or check out features like Change Data Capture in SQL Server 2008 and newer.

---

Related Topics