Gaby Abed (2/27/2009)

---

How many rows would this return? :) (yes...it's different)

use AdventureWorks
go
declare @tableA TABLE(col1 int, col2 int)
go
--stmt 1
select name from tempdb.sys.tables where name like '%tableA%'
--stmt 2
select name from tempdb.sys.columns where name like 'col1%'

Chad Crawford (2/27/2009)

---

I must admit - you TOTALLY had me... I was sitting here going "what??!?!", nothing is different! I even pasted the selects next to each other to make sure they were the same and hadn't changed. So why the different results?

I guess you can tell me "where to go". :D
GOT ME!

Chad

insert into @someTableVariable
  exec someStoredProcecdure

WayneS (2/27/2009)

---

Interesting.

I modified the supplied code to get the # of tempdb objects, and all tempdb table names:

use AdventureWorks
go
select count(*) from tempdb.sys.objects
select name from tempdb.sys.tables --where name like '%tableA%'
declare @tableA TABLE(col1 int, col2 int)
--stmt 1
select name from tempdb.sys.tables --where name like '%tableA%'
--stmt 2
select name from tempdb.sys.columns where name like 'col1%'
select count(*) from tempdb.sys.objects

and I find that there are no new tables or objects between the two selects from sys.tables.

So I modified the sys.columns select to:

select sc.name [ColName], st.name [TableName] 
  from tempdb.sys.columns sc
    INNER JOIN tempdb.sys.tables st ON st.object_id = sc.object_id
 where sc.name like 'col1%'

What I'm seeing is that the tablename for this table is already in use before the table is declared. I've even tried stopping and restarting the sql service for this instance.

What's up with this?