2

i am trying to identify the columns of database which has default value NULL or the default value has not been set. for that i am executing the query like..

SELECT T.TABLE_NAME,CL.COLUMN_NAME,CL.DATA_DEFAULT

    FROM   SYS.USER_TABLES T
               JOIN SYS.USER_TAB_COLUMNS CL ON T.TABLE_NAME = CL.TABLE_NAME

    WHERE  CL.DATA_DEFAULT IS NULL

    ORDER  BY t.table_name

in output of this query, i am getting the column whose default value is not set (empty), but i am not getting the columns whose default value is NULL

MKDoshi
  • 57
  • 6
  • If a column is `nullable` and no other default value is set then `NULL` will be the default value. See `SYS.USER_TAB_COLUMNS.NULLABLE` – PM 77-1 Apr 10 '13 at 06:24

4 Answers4

5

You can try with following query also - 

WITH xml AS (
         SELECT XMLTYPE(
                    DBMS_XMLGEN.GETXML('SELECT table_name, column_name ,DATA_DEFAULT FROM user_tab_cols')
                    ) AS xml
          FROM   dual
          )
SELECT * FROM ( 
 SELECT extractValue(xs.object_value, '/ROW/TABLE_NAME')       AS table_name
  ,      extractValue(xs.object_value, '/ROW/COLUMN_NAME')      AS column_name
  ,      extractValue(xs.object_value, '/ROW/DATA_DEFAULT')  AS DATA_DEFAULT
  FROM   xml x
  ,      TABLE(XMLSEQUENCE(EXTRACT(x.xml, '/ROWSET/ROW'))) xs
  ) WHERE (DATA_DEFAULT IS NULL OR UPPER(DATA_DEFAULT) = 'NULL')
;

here is some problem with the query above that you need to spend more time to get the result, but you will get correct result as per expected.

pratik garg
  • 3,282
  • 1
  • 17
  • 21
4

In oracle (at least for 11g) if you don't give a default value to a column, it's like giving it null.

See this example

UPDATE Thanks to @pratik garg comment, I now understand that what you're reaaly asking for, is a way to query a long column.
Since as @PratikGrag stated when you define defualt null the LONG column DATA_DEFAULT will contain 'null'.

Since USER_TAB_COLUMNS has got no available ROWID, the only way I can think of is by using PLSQL.

Something like this:

BEGIN
FOR rec IN (SELECT T.TABLE_NAME,CL.COLUMN_NAME,CL.DATA_DEFAULT
    FROM   SYS.USER_TABLES T
               JOIN SYS.USER_TAB_COLUMNS CL ON T.TABLE_NAME = CL.TABLE_NAME
   ORDER  BY t.table_name) LOOP

  IF rec.DATA_DEFAULT = 'null' THEN
    dbms_output.put_line(rec.table_name || ' ' || rec.COLUMN_NAME);
  END IF;
END LOOP;
END;
A.B.Cade
  • 16,735
  • 1
  • 37
  • 53
  • What about **SQL Server**? – Prahalad Gaggar Apr 10 '13 at 06:18
  • 1
    @Luv, I don't know much about **SQL SERVER**, but according to [documentation](http://msdn.microsoft.com/en-us/library/ms187872.aspx) it will be `null` as well – A.B.Cade Apr 10 '13 at 06:27
  • 2
    @A.B.Cade if you are explicitly defining the default value as null then the data_default (long) column will store Null as value, not empty string. that's why if you have such constraints in your database then those columns will not be showing in the query posted in question – pratik garg Apr 10 '13 at 06:27
  • @A.B.Cade in output of your example, i dont see the column 'a' of table 'A'. i wan't that too. – MKDoshi Apr 10 '13 at 06:52
  • @MKDoshi, I didn't really understand your question at first, now I think I do. See my update – A.B.Cade Apr 10 '13 at 07:10
0

There is, 

SELECT T.TABLE_NAME,CL.COLUMN_NAME,CL.DATA_DEFAULT

FROM   SYS.USER_TABLES T
           JOIN SYS.USER_TAB_COLUMNS CL ON T.TABLE_NAME = CL.TABLE_NAME

WHERE  ( CL.DATA_DEFAULT IS NULL OR LENGTH(CL.DATA_DEFAULT || '') < 1 )

ORDER  BY t.table_name
jiho
  • 1
  • 1
0

Empty Means NULL its one and the same thing...

Mukesh Dhiman
  • 99
  • 7