• 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?

    I think the (lack of ) batch separator is causing this.

    Try this:

    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%'

    declare @tableA TABLE(col1 int, col2 int)

    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%'

    In the above, although we expect to see data for only the second select, we get data from both selects.

    In the below select, if you separate the select between batches then you can see the count change.

    select count(*) from tempdb.sys.objects

    go

    declare @tableA TABLE(col1 int, col2 int)

    select count(*) from tempdb.sys.objects

    go

    [font="Courier New"]Sankar Reddy | http://SankarReddy.com/[/url][/font]