SQL Server-ing you

A blog about SQL Server

Archive for May 2009

How to get a table definition if it’s a temporary table

leave a comment »

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.

Advertisements

Written by Nicolas de Fontenay

2009/05/06 at 5:00 am

Welcome to SQL Server Whore

leave a comment »

Hello everyone.

passers by or people with a problem (presumably with SQL Server) are all welcome here.
Except those posting comments about enlarging there penis.

I was a DBA on Oracle Server (I still am but I don’t use it much these days). Since november last year I’ve been working with SQL Server 2005 and soon 2008.

That’s quite a big change. I’ve found out a lot about Microsoft philosophy on a lot of things and I want to use this blog mainly as my lifetime repository of ideas for SQL Server.

What you can expect here in the coming future are queries to make life easier (but hopefully SQL Server 2008 solves that part), my own thoughts about query optimization, a bit of query analysis with the execution plan and certainly some oracle/sql server comparisons.

Written by Nicolas de Fontenay

2009/05/06 at 4:42 am

Posted in General