Export (0) Print
Expand All
Expand Minimize
This topic has not yet been rated - Rate this topic

getColumns Method (SQLServerDatabaseMetaData)

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


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

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.

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 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.1

SS_IS_COLUMN_SET

smallint

If the column is the sparse column_set column, this has the value 1; otherwise, 0. 1

SS_IS_COMPUTED

smallint

Indicates if a column in a TABLE_TYPE is a computed column. 1

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. 1

SS_UDT_CATALOG_NAME

String

The name of the catalog that contains the user-defined type (UDT). 1

SS_UDT_SCHEMA_NAME

String

The name of the schema that contains the user-defined type (UDT). 1

SS_UDT_ASSEMBLY_TYPE_NAME

String

The fully-qualified name user-defined type (UDT). 1

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. 1

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. 1

SS_XML_SCHEMACOLLECTION_NAME

String

The name of an XML schema collection. If the name cannot be found, this is an empty string. 1

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.

Note 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) and Associated Numeric Constant

Return Type in JDBC Driver 3.0 when connected to SQL Server 2008 or later

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

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

import java.sql.*;
public class c1 {
   public static void main(String[] args) {
      String connectionUrl = "jdbc:sqlserver://localhost:1433;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 {}
   }
}
Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft. All rights reserved.