Post reply

Temporary tables details in tempdb.

  • September 30, 2008 at 7:04 am

    #198791

    In my tempdb database I can see a hell lot of Temporary tables. Want to know the details of these temporary tables like which table it refers to , who created it ? Is it system defined or user define ?

  • September 30, 2008 at 10:21 am

    #878976

    any table names in particular? Are you able to query the tables directly? You can use the system tables to query the columns etc, here is an example of them in use, just define the schema name, object/view name etc..

    SELECT

    CAST(sys.all_columns.name AS NVARCHAR(255)), CAST(COALESCE(CASE WHEN sys.extended_properties.value = '' THEN sys.all_columns.name ELSE sys.extended_properties.value END,sys.all_columns.name) AS NVARCHAR(255))

    FROM

    sys.all_columns

    INNER JOIN

    sys.all_objects ON sys.all_columns.object_id = sys.all_objects.object_id

    INNER JOIN

    sys.schemas

    ON sys.all_objects.schema_id = sys.schemas.schema_id

    INNER JOIN

    sys.extended_properties

    ON sys.all_columns.object_id = sys.extended_properties.major_id

    AND

    sys.all_columns.column_id = sys.extended_properties.minor_id

    WHERE

    (sys.all_objects.name = @myViewName)

    AND (sys.schemas.name = @mySchemaName)

    AND (sys.extended_properties.name = N'uepReportingName')

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply