Get Temp Table definition
-
Comments posted to this topic are about the item Get Temp Table definition
Wes
(A solid design is always preferable to a creative workaround) -
I like this - thank you
-
Thanks very much, very useful.
-
Apparently with some data types or some SQL versions, some columns may be duplicated with a sysname datatype. I've submitted an update to the original script, but it may take a few days for the update to be approved.
In the Results CTE
FROM tempdb.sys.columns AS C
INNER JOIN tempdb.sys.types AS T2
ON C.system_type_id = T2.system_type_idchange the ON clause above to this
ON c.user_type_id = t2.user_type_idWes
(A solid design is always preferable to a creative workaround) -
You can
exec tempdb.dbo.sp_help '#temp'
and you have all details about #tempIgor Micev,My blog: www.igormicev.com
- Igor Micev - Wednesday, August 15, 2018 7:59 AM
Agreed. And it's excellent for designing and troubleshooting. It is, however, a bit difficult to automate if you're dealing with unknown inputs that are present in the Temp Table because you used SELECT/INTO to create the Temp Table and you need your code to be able to read the results so that it may make decisions about the data based on meta-data in the Temp Table.
As a bit of a sidebar, I think that its a shame that MS doesn't have a separate extended procedure that can actually be used to return an easily consumable meta-data table about any table.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
I really like this script. Just been using it to create definitions or temporary tables that have been created using INTO #... which later had a problem with NULL columns when I tried to create a primary key on them. So it allows me to change the definition to be NOT NULL.
- Jonathan AC Roberts - Thursday, December 20, 2018 9:13 AM
If you want to create NOT NULL columns in a table created by SELECT/INTO, just encapsulate the items in the SELECT list with a viable ISNULL function even if its impossible to have a NULL in the column.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - Jeff Moden - Thursday, December 20, 2018 1:06 PM
Thanks, sounds a better way of doing it.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply