查询一个表的列数

Question

我现在需要查询一个数据表的列数，请问一个SQL语句怎么实现啊？

Answer 1

SELECT COUNT(*) FROM user_tab_columns
WHERE table_name= ?;

Answer 2

select count(column_id)
from user_tab_columns
where table_name='YOUR_TABLE_NAME';-- 注意表名的大小写格式！(默认表名应该是大写)

Answer 3

--1.通过查看表可以知道列数
SQL> desc emp;
Name     Type         Nullable Default Comments 
-------- ------------ -------- ------- -------- 
EMPNO    NUMBER(4)                              
ENAME    VARCHAR2(10) Y                         
JOB      VARCHAR2(9)  Y                         
MGR      NUMBER(4)    Y                         
HIREDATE DATE         Y                         
SAL      NUMBER(7,2)  Y                         
COMM     NUMBER(7,2)  Y                         
DEPTNO   NUMBER(2)    Y                         --2.通过计算user_tab_columns视图中的column_id的个数
SQL> select count(column_id)
  2  from user_tab_columns
  3  where table_name='EMP';COUNT(COLUMN_ID)
----------------
               8
--我们来看看user_tab_columns视图的结构
SQL> desc user_tab_columns;
Name                 Type          Nullable Default Comments                                                             
-------------------- ------------- -------- ------- -------------------------------------------------------------------- 
TABLE_NAME           VARCHAR2(30)                   Table, view or cluster name                                          
COLUMN_NAME          VARCHAR2(30)                   Column name                                                          
DATA_TYPE            VARCHAR2(106) Y                Datatype of the column                                               
DATA_TYPE_MOD        VARCHAR2(3)   Y                Datatype modifier of the column                                      
DATA_TYPE_OWNER      VARCHAR2(30)  Y                Owner of the datatype of the column                                  
DATA_LENGTH          NUMBER                         Length of the column in bytes                                        
DATA_PRECISION       NUMBER        Y                Length: decimal digits (NUMBER) or binary digits (FLOAT)             
DATA_SCALE           NUMBER        Y                Digits to right of decimal point in a number                         
NULLABLE             VARCHAR2(1)   Y                Does column allow NULL values?                                       
COLUMN_ID            NUMBER        Y                Sequence number of the column as created                             
DEFAULT_LENGTH       NUMBER        Y                Length of default value for the column                               
DATA_DEFAULT         LONG          Y                Default value for the column                                         
NUM_DISTINCT         NUMBER        Y                The number of distinct values in the column                          
LOW_VALUE            RAW(32)       Y                The low value in the column                                          
HIGH_VALUE           RAW(32)       Y                The high value in the column                                         
DENSITY              NUMBER        Y                The density of the column                                            
NUM_NULLS            NUMBER        Y                The number of nulls in the column                                    
NUM_BUCKETS          NUMBER        Y                The number of buckets in histogram for the column                    
LAST_ANALYZED        DATE          Y                The date of the most recent time this column was analyzed            
SAMPLE_SIZE          NUMBER        Y                The sample size used in analyzing this column                        
CHARACTER_SET_NAME   VARCHAR2(44)  Y                Character set name                                                   
CHAR_COL_DECL_LENGTH NUMBER        Y                Declaration length of character type column                          
GLOBAL_STATS         VARCHAR2(3)   Y                Are the statistics calculated without merging underlying partitions? 
USER_STATS           VARCHAR2(3)   Y                Were the statistics entered directly by the user?                    
AVG_COL_LEN          NUMBER        Y                The average length of the column in bytes                            
CHAR_LENGTH          NUMBER        Y                The maximum length of the column in characters                       
CHAR_USED            VARCHAR2(1)   Y                C is maximum length given in characters, B if in bytes               
V80_FMT_IMAGE        VARCHAR2(3)   Y                Is column data in 8.0 image format?                                  
DATA_UPGRADED        VARCHAR2(3)   Y                Has column data been upgraded to the latest type version format?     
HISTOGRAM            VARCHAR2(15)  Y

Answer 4

SELECT COUNT(*) FROM user_tab_columns
WHERE table_name= '大写表名';

Answer 5

SELECT COUNT(*) FROM user_tab_columns
WHERE table_name=?

Answer 6

这个很多人都知道是：
select count(1) from user_tab_columns where table_name= 'yourtablename'

Answer 7

select count(column_id) from user_tab_columns where table_name='EMP';
表名大写