getColumns Method (SQLServerDatabaseMetaData)

Article
12/16/2022

Retrieves a description of the table columns that are available in the specified catalog.

Syntax

  
public java.sql.ResultSet getColumns(java.lang.String catalog,  
                                     java.lang.String schema,  
                                     java.lang.String table,  
                                     java.lang.String col)

Parameters

catalog

A String that contains the catalog name.

schema

A String that contains the schema name pattern.

table

A String that contains the table name pattern.

col

A String that contains the column name pattern.

Return Value

A SQLServerResultSet object.

Exceptions

SQLServerException

Remarks

This getColumns method is specified by the getColumns method in the java.sql.DatabaseMetaData interface.

The result set returned by the getColumns method will contain the following information:

Name	Type	Description
TABLE_CAT	String	The catalog name.
TABLE_SCHEM	String	The table schema name.
TABLE_NAME	String	The table name.
COLUMN_NAME	String	The column name.
DATA_TYPE	smallint	The SQL data type from java.sql.Types.
TYPE_NAME	String	The name of the data type.
COLUMN_SIZE	int	The precision of the column.
BUFFER_LENGTH	smallint	Transfer size of the data.
DECIMAL_DIGITS	smallint	The scale of the column.
NUM_PREC_RADIX	smallint	The radix of the column.
NULLABLE	smallint	Indicates if the column is nullable. It can be one of the following values: columnNoNulls (0) columnNullable (1)
REMARKS	String	The comments associated with the column. Note: SQL Server always returns null for this column.
COLUMN_DEF	String	The default value of the column.
SQL_DATA_TYPE	smallint	Value of the SQL data type as it appears in the TYPE field of the descriptor. This column is the same as the DATA_TYPE column, except for the datetime and SQL-92 interval data types. This column always returns a value.
SQL_DATETIME_SUB	smallint	Subtype code for datetime and SQL-92 interval data types. For other data types, this column returns NULL.
CHAR_OCTET_LENGTH	int	The maximum number of bytes in the column.
ORDINAL_POSITION	int	The index of the column within the table.
IS_NULLABLE	String	Indicates if the column allows null values.
SS_IS_SPARSE	smallint	If the column is a sparse column, this has the value 1; otherwise, 0.¹
SS_IS_COLUMN_SET	smallint	If the column is the sparse column_set column, this has the value 1; otherwise, 0. ¹
SS_IS_COMPUTED	smallint	Indicates if a column in a TABLE_TYPE is a computed column. ¹
IS_AUTOINCREMENT	String	"YES" if the column is auto incremented. "NO" if the column is not auto incremented. "" (empty string) if the driver cannot determine if the column is auto incremented. ¹
SS_UDT_CATALOG_NAME	String	The name of the catalog that contains the user-defined type (UDT). ¹
SS_UDT_SCHEMA_NAME	String	The name of the schema that contains the user-defined type (UDT). ¹
SS_UDT_ASSEMBLY_TYPE_NAME	String	The fully-qualified name user-defined type (UDT). ¹
SS_XML_SCHEMACOLLECTION_CATALOG_NAME	String	The name of the catalog where an XML schema collection name is defined. If the catalog name cannot be found, this variable contains an empty string. ¹
SS_XML_SCHEMACOLLECTION_SCHEMA_NAME	String	The name of the schema where an XML schema collection name is defined. If the schema name cannot be found, this is an empty string. ¹
SS_XML_SCHEMACOLLECTION_NAME	String	The name of an XML schema collection. If the name cannot be found, this is an empty string. ¹
SS_DATA_TYPE	tinyint	The SQL Server data type that is used by extended stored procedures. Note For more information about the data types returned by SQL Server, see "Data Types (Transact-SQL)" in SQL Server Books Online.

(1) This column will not be present if you are connecting to SQL Server 2005 (9.x).

Note

For more information about the data returned by the getColumns method, see "sp_columns (Transact-SQL)" in SQL Server Books Online.

In the Microsoft SQL Server JDBC Driver 3.0, you will see the following behavior changes from earlier versions of the JDBC Driver:

The DATA_TYPE column has the following changes:

SQL Server Data Type	Return Type in JDBC Driver 2.0 (or, if connected to SQL Server 2005 (9.x)) and Associated Numeric Constant	Return Type in JDBC Driver 3.0 when connected to SQL Server 2008 (10.0.x) and later versions
user-defined type larger than 8 kB	LONGVARBINARY (-4)	VARBINARY (-3)
geography	LONGVARBINARY (-4)	VARBINARY (-3)
geometry	LONGVARBINARY (-4)	VARBINARY (-3)
varbinary(max)	LONGVARBINARY (-4)	VARBINARY (-3)
nvarchar(max)	LONGVARCHAR (-1) or LONGNVARCHAR (JDBC 4) (-16)	VARCHAR (12) or NVARCHAR (JDBC 4) (-9)
varchar(max)	LONGVARCHAR (-1)	VARCHAR (12)
time	VARCHAR (12) or NVARCHAR (JDBC 4) (-9)	TIME (-154)
date	VARCHAR (12) or NVARCHAR (JDBC 4) (-9)	DATE (91)
datetime2	VARCHAR (12) or NVARCHAR (JDBC 4) (-9)	TIMESTAMP (93)
datetimeoffset	VARCHAR (12) or NVARCHAR (JDBC 4) (-9)	microsoft.sql.Types.DATETIMEOFFSET (-155)

The COLUMN_SIZE column has the following changes:

SQL Server Data Type	Return Type in JDBC Driver 2.0	Return Type in JDBC Driver 3.0
nvarchar(max)	1073741823	2147483647 (database metadata)
xml	1073741823	2147483647 (database metadata)
user-defined type less than or equal to 8 kB	8 kB (result set and parameter metadata)	Actual size returned by the stored procedure.
time		The length in characters of the string representation of the type, assuming the maximum allowed precision of the fractional seconds' component.
date		same as time
datetime2		same as time
datetimeoffset		same as time

The BUFFER_LENGTH column has the following change:

SQL Server Data Type	Return Type in JDBC Driver 2.0	Return Type in JDBC Driver 3.0
user-defined type larger than 8 kB		2147483647

The TYPE_NAME column has the following changes:

SQL Server Data Type	Return Type in JDBC Driver 2.0	Return Type in JDBC Driver 3.0
varchar(max)	text	varchar
varbinary(max)	image	varbinary

The DECIMAL_DIGITS column has the following changes:

SQL Server Type	JDBC Driver 2.0	JDBC Driver 3.0
time	null	7 (or smaller if specified)
date	null	null
datetime2	null	7 (or smaller if specified)
datetimeoffset	null	7 (or smaller if specified)

The SQL_DATA_TYPE column has the following changes:

SQL Server Data Type	SQL Server 2008 Data Value in JDBC Driver 2.0	SQL Server 2008 Data Value in JDBC Driver 3.0
varchar(max)	-10	-9
nvarchar(max)	-1	-9
xml	-10	-152
user-defined type less than or equal to 8 kB	-3	-151
user-defined type larger than 8 kB	Not available in JDBC Driver 2.0	-151
geography	-4	-151
geometry	-4	-151
hierarchyid	-4	-151
time	-9	92
date	-9	91
datetime2	-9	93
datetimeoffset	-9	-155

Example

The following example demonstrates how to use the getColumns method to return information for the Person.Contact table in the AdventureWorks2022 sample database.

import java.sql.*;  
public class c1 {  
   public static void main(String[] args) {  
      String connectionUrl = "jdbc:sqlserver://localhost:1433;encrypt=true;databaseName=AdventureWorks;integratedsecurity=true";  
  
      Connection con = null;  
      Statement stmt = null;  
      ResultSet rs = null;  
  
      try {  
         Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");  
         con = DriverManager.getConnection(connectionUrl);  
         DatabaseMetaData dbmd = con.getMetaData();  
         rs = dbmd.getColumns("AdventureWorks", "Person", "Contact", "FirstName");  
  
         ResultSet r = dbmd.getColumns(null, null, "Contact", null);  
         ResultSetMetaData rm = r.getMetaData();   
         int noofcols = rm.getColumnCount();  
  
         if (r.next())  
            for (int i = 0 ; i < noofcols ; i++ )  
            System.out.println(rm.getColumnName( i + 1 ) + ": \t\t" + r.getString( i + 1 ));  
      }  
  
      catch (Exception e) {}  
      finally {}  
   }  
}

Share via