How to get the number of columns of a temporary table

Asked

Viewed 878 times

3

How is it possible to obtain the number of existing columns in a given temporary table in sql server?

sql sql-server

2 answers

6


Try using the following SQL:

SELECT COUNT(COLUMN_NAME) 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_CATALOG = 'database' AND TABLE_SCHEMA = 'dbo'
AND TABLE_NAME = 'sua_tabela'

For temporary tables, use:

SELECT COUNT(*)
FROM tempdb.sys.columns
WHERE object_id = object_id('tempdb..#tmp_grid_email')

  • Didn’t work :/

  • create table #tmp_grid_email ( id int Primary key, nvarchar(30), nvarchar(50)&#Xa address;)

  • using this table for testing and returned zero instead of 3

  • The problem is that it is a temporary table

  • 1

    Ran valeuuuu

1

Hello, see if the code below meets your need.

-- ==============================
-- exibindo qtd de colunas
-- ==============================

SELECT COUNT(*) AS QTD
FROM 
    sys.sysobjects    AS T (NOLOCK) 
INNER JOIN sys.all_columns AS C (NOLOCK) ON T.id = C.object_id AND T.XTYPE = 'U' 
WHERE 
    T.NAME LIKE '%NOME_TABELA%'

-- ==============================
-- exibindo as colunas
-- ==============================

SELECT 
    T.name AS Tabela, 
    C.name AS Coluna
FROM 
    sys.sysobjects    AS T (NOLOCK) 
INNER JOIN sys.all_columns AS C (NOLOCK) ON T.id = C.object_id AND T.XTYPE = 'U' 
WHERE 
    T.NAME LIKE '%NOME_TABELA%'
ORDER BY 
    C.column_id ASC

Browser other questions tagged sql sql-server

You are not signed in. Login or sign up in order to post.