Skip to Content
Author's profile photo Eduardo Rezende

Do you need to check which are the columns for a particular table?

If you need to check which columns are in a particular table, with Oracle databases you can use command desc:

SQL> desc “SAPGFP”.”T000″

Name

Null? Type

MANDT

NOT NULL VARCHAR2(9)
MTEXT NOT NULL VARCHAR2(75)
ORT01 NOT NULL VARCHAR2(75)
MWAER NOT NULL VARCHAR2(15)
ADRNR NOT NULL VARCHAR2(30)
CCCATEGORY NOT NULL VARCHAR2(3)
CCCORACTIV NOT NULL VARCHAR2(3)
CCNOCLIIND NOT NULL VARCHAR2(3)
CCCOPYLOCK

NOT NULL

VARCHAR2(3)
CCNOCASCAD NOT NULL VARCHAR2(3)
CCSOFTLOCK NOT NULL VARCHAR2(3)
CCORIGCONT NOT NULL VARCHAR2(3)
CCIMAILDIS NOT NULL VARCHAR2(3)
CCTEMPLOCK NOT NULL VARCHAR2(3)
CHANGEUSER NOT NULL VARCHAR2(36)
CHANGEDATE NOT NULL VARCHAR2(24)
LOGSYS NOT NULL VARCHAR2(30)

Within SQL Server we do not have the same command but we can use the following statement:

select COLUMN_NAME, IS_NULLABLE, DATA_TYPE
from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME='T000'

* More columns from INFORMATION_SCHEMA.COLUMNS can be selected if necessary

COLUMN_NAME

IS_NULLABLE DATA_TYPE

MANDT

NO NVARCHAR
MTEXT NO NVARCHAR
ORT01 NO NVARCHAR
MWAER NO NVARCHAR
ADRNR NO NVARCHAR
CCCATEGORY NO NVARCHAR
CCCORACTIV NO NVARCHAR
CCNOCLIIND NO NVARCHAR
CCCOPYLOCK

NO

NVARCHAR
CCNOCASCAD NO NVARCHAR
CCSOFTLOCK NO NVARCHAR
CCORIGCONT NO NVARCHAR
CCIMAILDIS NO NVARCHAR
CCTEMPLOCK NO NVARCHAR
CHANGEUSER NO NVARCHAR
CHANGEDATE NO NVARCHAR
LOGSYS NO NVARCHAR

Also, sp_help (much more information about the object) can be used:

sp_help 'sm1.T000'

Results in the attached file sp_help T000.txt.zip.

And of course use transaction DBACockpit:

DBACockpit.png

Hope you like this blog and any comment/suggestion are welcome!

Regards,

Eduardo

Assigned Tags

      1 Comment
      You must be Logged on to comment or reply to a post.
      Author's profile photo Thiago Cavalheiro
      Thiago Cavalheiro

      Nice! Thanks for sharing it!