A metadata function mentioned in a computed column in a #temp
table is going to reference tempdb.sys.objects
, not yourdatabase.sys.objects
, since the table is created in tempdb
. Try adding the database prefix to the insert, and that will make OBJECT_ID
go find it in the right place:
CREATE TABLE #procs (
name SYSNAME,
[object_id] AS OBJECT_ID(name)
);
INSERT INTO #procs (name) VALUES ('dbo.sp_send_dbmail');
INSERT INTO #procs (name) VALUES ('msdb.dbo.sp_send_dbmail');
SELECT name, [object_id] FROM #procs;
Results:
dbo.sp_send_dbmail NULL
msdb.dbo.sp_send_dbmail 283148054
We have had discussions about this many times. The information schema serves certain purposes. If you know your way around the system catalogs, those serve most purposes better, IMO. The system catalogs are the actual source of all information.
The information schema provides standardized views which help with portability, mostly across major Postgres versions, because portability across different RDBMS platforms typically is an illusion once your queries are sophisticated enough to need to look up system catalogs. And, notably, Oracle still doesn't support the information schema.
Views in the information schema must jump through many hoops to achieve a format complying to the standard. This makes them slow, sometimes very slow. Compare plans and performance for these basic objects:
EXPLAIN ANALYZE SELECT * from information_schema.columns;
EXPLAIN ANALYZE SELECT * from pg_catalog.pg_attribute;
The difference is remarkable. It really depends on what you are looking for.
Your example
For your example SELECT * from tbl
, compare the two queries below for this simple table:
CREATE TEMP TABLE foo(
A numeric(12,3)
, b timestamp(0)
);
Using pg_attribute
:
SELECT attname, format_type(atttypid, atttypmod) AS type
FROM pg_attribute
WHERE attrelid = 'foo'::regclass
AND attnum > 0
AND NOT attisdropped
ORDER BY attnum;
format_type()
returns the complete type with all modifiers:
attname | type
--------+-------------------------------
a | numeric(12,3)
b | timestamp(0) without time zone
Also note that the cast to regclass
resolves the table name somewhat intelligently according to the current search_path
. It also raises an exception if the name is not valid. Details:
Using information_schema.columns
:
SELECT column_name, data_type
FROM information_schema.columns
WHERE table_name = 'foo'
ORDER BY ordinal_position;
The information is standardized, but incomplete:
column_name | data_type
------------+----------------------------
a | numeric
b | timestamp without time zone
To get full information for the data type you need to consider all of these columns additionally:
character_maximum_length
character_octet_length
numeric_precision
numeric_precision_radix
numeric_scale
datetime_precision
interval_type
interval_precision
Related answers:
A list of pros & cons, the biggest pros (IMO) in bold:
Information schema views
- often simpler (depends)
- slow
- preprocessed, which may or may not suit your needs
- selective (users only see objects they have privileges for)
- conforming to an SQL standard (that's implemented by some of the major RDBMS)
- mostly portable across major Postgres versions
- do not require much specific knowledge about Postgres
- identifiers are descriptive, long and sometimes awkward
System catalogs
- often more complex (depends), closer to the source
- fast
- complete (system columns like
oid
included)
- not complying to an SQL standard
- less portable across major Postgres versions (but basics aren't going to change)
- require more specific knowledge about Postgres
- identifiers are terse, less descriptive but conveniently short
Arbitrary query
To get the same list of column names and types from a query, you could use a simple trick: CREATE a temporary table from the query output, then use the same techniques as above.
You can append LIMIT 0
, since you do not need actual data:
CREATE TEMP TABLE tmp123 AS
SELECT 1::numeric, now()
LIMIT 0;
To get the data type of individual columns, you can also use the function pg_typeof()
:
SELECT pg_typeof(1);
Best Answer
Specifically for objects, there is a DMV called sys.dm_exec_describe_first_result_set_for_object which will describe the first result set if SQL Server can figure out what it should be (dynamic SQL, for example won't return a valid result).
Specifically for T-SQL or batch related items there is a different DMV and accompanied system stored procedure. The DMV is sys.dm_exec_describe_first_result_set and the stored procedure that parallels the dmv is sp_describe_first_result_set.