SQL Server-ing you

It works pretty much like for any other tables. You will need to be using the correct database first:

USE MyDatabase
GO

select            a.name, b.name, c.name, b.max_length, b.[precision], b.scale
from              sys.objects a
inner join    sys.columns b
on                   a.[object_id] = b.[object_id]
inner join    sys.types c
on                   b.system_type_id = c.system_type_id
where           [type] = ‘U’
and                a.name = ‘some_table_name’
order by      column_id

If it’s a temporary table however there’s a little trick.

The first thing to change is trivial:

USE tempdb
GO

select            a.name, b.name, c.name, b.max_length, b.[precision], b.scale
from              sys.objects a
inner join    sys.columns b
on                   a.[object_id] = b.[object_id]
inner join    sys.types c
on                   b.system_type_id = c.system_type_id
where           [type] = ‘U’
and                a.name like  ‘#temp%’
order by      column_id

Things to know:

1) A temporary table is a user generated table and therefore its type is ‘U’
2) The result can be a lot more than what you would expect. For #temp, you can get #temp_____________________________________________0000000039A1

This is to ensure that 2 tables with identical names from 2 different users won’t be created.