I have a SQL Server database and I want to know what columns and types it has. I'd prefer to do this through a query rather than using a GUI like Enterprise Manager. Is there a way to do this?

2

25 Answers

You can use the sp_columns stored procedure:

exec sp_columns MyTable

9

There are a few methods to get metadata about a table:

EXEC sp_help tablename

Will return several result sets, describing the table, it's columns and constraints.

The INFORMATION_SCHEMA views will give you the information you want, though unfortunately you have to query the views and join them manually.

5

Just in case you don't want to use stored proc, here's a simple query version

select * from information_schema.columns where table_name = 'aspnet_Membership' order by ordinal_position

3

You can use following: sp_help tablename

Example: sp_help Customer

OR Use Shortcut Keys

• Select the desired table and press ALT+F1.

Example: Customer Press ALT+F1.

2

Use this Query

Select * From INFORMATION_SCHEMA.COLUMNS Where TABLE_NAME = 'TABLENAME'

3

In addition to the ways shown in other answers, you can use

SELECT TOP 0 * FROM table_name

This will give you the name of each column with no results in them, and completes almost instantly with minimal overhead.

1

Please use the following sql query; this worked for my case.

select * FROM INFORMATION_SCHEMA.Columns where table_name = 'tablename';

1

Just select table and press Alt+F1,

it will show all the information about table like Column name, datatype, keys etc.

2

I wrote an sql*plus DESC(RIBE) like select (displays the column comments, too) in t-sql:

USE YourDB
GO
DECLARE @objectName NVARCHAR(128) = 'YourTable';
SELECT a.[NAME] ,a.[TYPE] ,a.[CHARSET] ,a.[COLLATION] ,a.[NULLABLE] ,a.[DEFAULT] ,b.[COMMENTS]
-- ,a.[ORDINAL_POSITION]
FROM ( SELECT COLUMN_NAME AS [NAME] ,CASE DATA_TYPE WHEN 'char' THEN DATA_TYPE + '(' + CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR) + ')' WHEN 'numeric' THEN DATA_TYPE + '(' + CAST(NUMERIC_PRECISION AS VARCHAR) + ', ' + CAST(NUMERIC_SCALE AS VARCHAR) + ')' WHEN 'nvarchar' THEN DATA_TYPE + '(' + CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR) + ')' WHEN 'varbinary' THEN DATA_TYPE + '(' + CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR) + ')' WHEN 'varchar' THEN DATA_TYPE + '(' + CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR) + ')' ELSE DATA_TYPE END AS [TYPE] ,CHARACTER_SET_NAME AS [CHARSET] ,COLLATION_NAME AS [COLLATION] ,IS_NULLABLE AS [NULLABLE] ,COLUMN_DEFAULT AS [DEFAULT] ,ORDINAL_POSITION FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @objectName ) a FULL JOIN ( SELECT CAST(value AS NVARCHAR) AS [COMMENTS] ,CAST(objname AS NVARCHAR) AS [NAME] FROM ::fn_listextendedproperty ('MS_Description', 'user', 'dbo', 'table', @objectName, 'column', default) ) b ON a.NAME COLLATE YourCollation = b.NAME COLLATE YourCollation
ORDER BY a.[ORDINAL_POSITION];

The above mentioned select can be used in a system marked stored procedure and it can be called from any database of your instance on a simple way:

USE master;
GO
IF OBJECT_ID('sp_desc', 'P') IS NOT NULL DROP PROCEDURE sp_desc
GO
CREATE PROCEDURE sp_desc ( @tableName nvarchar(128)
) AS
BEGIN DECLARE @dbName sysname; DECLARE @schemaName sysname; DECLARE @objectName sysname; DECLARE @objectID int; DECLARE @tmpTableName varchar(100); DECLARE @sqlCmd nvarchar(4000); SELECT @dbName = PARSENAME(@tableName, 3); IF @dbName IS NULL SELECT @dbName = DB_NAME(); SELECT @schemaName = PARSENAME(@tableName, 2); IF @schemaName IS NULL SELECT @schemaName = SCHEMA_NAME(); SELECT @objectName = PARSENAME(@tableName, 1); IF @objectName IS NULL BEGIN PRINT 'Object is missing from your function call!'; RETURN; END; SELECT @objectID = OBJECT_ID(@dbName + '.' + @schemaName + '.' + @objectName); IF @objectID IS NULL BEGIN PRINT 'Object [' + @dbName + '].[' + @schemaName + '].[' + @objectName + '] does not exist!'; RETURN; END; SELECT @tmpTableName = '#tmp_DESC_' + CAST(@@SPID AS VARCHAR) + REPLACE(REPLACE(REPLACE(REPLACE(CAST(CONVERT(CHAR, GETDATE(), 121) AS VARCHAR), '-', ''), ' ', ''), ':', ''), '.', ''); --PRINT @tmpTableName; SET @sqlCmd = ' USE ' + @dbName + ' CREATE TABLE ' + @tmpTableName + ' ( [NAME] nvarchar(128) NOT NULL ,[TYPE] varchar(50) ,[CHARSET] varchar(50) ,[COLLATION] varchar(50) ,[NULLABLE] varchar(3) ,[DEFAULT] nvarchar(4000) ,[COMMENTS] nvarchar(3750)); INSERT INTO ' + @tmpTableName + ' SELECT a.[NAME] ,a.[TYPE] ,a.[CHARSET] ,a.[COLLATION] ,a.[NULLABLE] ,a.[DEFAULT] ,b.[COMMENTS] FROM ( SELECT COLUMN_NAME AS [NAME] ,CASE DATA_TYPE WHEN ''char'' THEN DATA_TYPE + ''('' + CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR) + '')'' WHEN ''numeric'' THEN DATA_TYPE + ''('' + CAST(NUMERIC_PRECISION AS VARCHAR) + '', '' + CAST(NUMERIC_SCALE AS VARCHAR) + '')'' WHEN ''nvarchar'' THEN DATA_TYPE + ''('' + CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR) + '')'' WHEN ''varbinary'' THEN DATA_TYPE + ''('' + CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR) + '')'' WHEN ''varchar'' THEN DATA_TYPE + ''('' + CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR) + '')'' ELSE DATA_TYPE END AS [TYPE] ,CHARACTER_SET_NAME AS [CHARSET] ,COLLATION_NAME AS [COLLATION] ,IS_NULLABLE AS [NULLABLE] ,COLUMN_DEFAULT AS [DEFAULT] ,ORDINAL_POSITION FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = ''' + @objectName + ''' ) a FULL JOIN ( SELECT CAST(value AS NVARCHAR) AS [COMMENTS] ,CAST(objname AS NVARCHAR) AS [NAME] FROM ::fn_listextendedproperty (''MS_Description'', ''user'', ''' + @schemaName + ''', ''table'', ''' + @objectName + ''', ''column'', default) ) b ON a.NAME COLLATE Hungarian_CI_AS = b.NAME COLLATE Hungarian_CI_AS ORDER BY a.[ORDINAL_POSITION]; SELECT * FROM ' + @tmpTableName + ';' --PRINT @sqlCmd; EXEC sp_executesql @sqlCmd; RETURN;
END;
GO
EXEC sys.sp_MS_marksystemobject sp_desc
GO

To execute the procedure type:

EXEC sp_desc 'YourDB.YourSchema.YourTable';

If you want to get a description an object of the current database (and schema) simple type:

EXEC sp_desc 'YourTable';

As sp_desc is a system marked procedure, you can even leave the exec command, too (not recommended anyway):

sp_desc 'YourTable';

The SQL Server equivalent to Oracle's describe command is the stored proc sp_help

The describe command gives you the information about the column names, types, length, etc.

In SQL Server, let's say you want to describe a table 'mytable' in schema 'myschema' in the database 'mydb', you can do following:

USE mydb;
exec sp_help 'myschema.mytable';

You can use the sp_help 'TableName'

try it:

EXEC [ServerName].[DatabaseName].dbo.sp_columns 'TableName'

and you can get some table structure information, such as:

TABLE_QUALIFIER, TABLE_OWNER, TABLE_NAME, COLUMN_NAME, DATA_TYPE, TYPE_NAME...

2

In addition to above questions, if we have table in DB like db_name.dbo.table_name, we may use following steps

1. Connect with DB

   USE db_name;

2. Use EXEC sp_help and don't forget to put table name as 'dbo.tablename' if you have dbo as schema.

   exec sp_help 'dbo.table_name'

This should work!

I tried this and it's working for me

exec sp_help TABLE_NAME

The problem with those answers is that you're missing the key info. While this is a bit messy this is a quick version I came up with to make sure it contains the same info the MySQL Describe displays.

Select SC.name AS 'Field', ISC.DATA_TYPE AS 'Type', ISC.CHARACTER_MAXIMUM_LENGTH AS 'Length', SC.IS_NULLABLE AS 'Null', I.is_primary_key AS 'Key', SC.is_identity AS 'Identity'
From sys.columns AS SC
LEFT JOIN sys.index_columns AS IC
ON IC.object_id = OBJECT_ID('dbo.Expenses') AND
IC.column_id = SC.column_id
LEFT JOIN sys.indexes AS I
ON I.object_id = OBJECT_ID('dbo.Expenses') AND
IC.index_id = I.index_id
LEFT JOIN information_schema.columns ISC
ON ISC.TABLE_NAME = 'Expenses'
AND ISC.COLUMN_NAME = SC.name
WHERE SC.object_id = OBJECT_ID('dbo.Expenses')

1

This is the code I use within the EntityFramework Reverse POCO Generator (available here)

Table SQL:

SELECT c.TABLE_SCHEMA AS SchemaName, c.TABLE_NAME AS TableName, t.TABLE_TYPE AS TableType, c.ORDINAL_POSITION AS Ordinal, c.COLUMN_NAME AS ColumnName, CAST(CASE WHEN IS_NULLABLE = 'YES' THEN 1 ELSE 0 END AS BIT) AS IsNullable, DATA_TYPE AS TypeName, ISNULL(CHARACTER_MAXIMUM_LENGTH, 0) AS [MaxLength], CAST(ISNULL(NUMERIC_PRECISION, 0) AS INT) AS [Precision], ISNULL(COLUMN_DEFAULT, '') AS [Default], CAST(ISNULL(DATETIME_PRECISION, 0) AS INT) AS DateTimePrecision, ISNULL(NUMERIC_SCALE, 0) AS Scale, CAST(COLUMNPROPERTY(OBJECT_ID(QUOTENAME(c.TABLE_SCHEMA) + '.' + QUOTENAME(c.TABLE_NAME)), c.COLUMN_NAME, 'IsIdentity') AS BIT) AS IsIdentity, CAST(CASE WHEN COLUMNPROPERTY(OBJECT_ID(QUOTENAME(c.TABLE_SCHEMA) + '.' + QUOTENAME(c.TABLE_NAME)), c.COLUMN_NAME, 'IsIdentity') = 1 THEN 1 WHEN COLUMNPROPERTY(OBJECT_ID(QUOTENAME(c.TABLE_SCHEMA) + '.' + QUOTENAME(c.TABLE_NAME)), c.COLUMN_NAME, 'IsComputed') = 1 THEN 1 WHEN DATA_TYPE = 'TIMESTAMP' THEN 1 ELSE 0 END AS BIT) AS IsStoreGenerated, CAST(CASE WHEN pk.ORDINAL_POSITION IS NULL THEN 0 ELSE 1 END AS BIT) AS PrimaryKey, ISNULL(pk.ORDINAL_POSITION, 0) PrimaryKeyOrdinal, CAST(CASE WHEN fk.COLUMN_NAME IS NULL THEN 0 ELSE 1 END AS BIT) AS IsForeignKey
FROM INFORMATION_SCHEMA.COLUMNS c LEFT OUTER JOIN (SELECT u.TABLE_SCHEMA, u.TABLE_NAME, u.COLUMN_NAME, u.ORDINAL_POSITION FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE u INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc ON u.TABLE_SCHEMA = tc.CONSTRAINT_SCHEMA AND u.TABLE_NAME = tc.TABLE_NAME AND u.CONSTRAINT_NAME = tc.CONSTRAINT_NAME WHERE CONSTRAINT_TYPE = 'PRIMARY KEY') pk ON c.TABLE_SCHEMA = pk.TABLE_SCHEMA AND c.TABLE_NAME = pk.TABLE_NAME AND c.COLUMN_NAME = pk.COLUMN_NAME LEFT OUTER JOIN (SELECT DISTINCT u.TABLE_SCHEMA, u.TABLE_NAME, u.COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE u INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc ON u.TABLE_SCHEMA = tc.CONSTRAINT_SCHEMA AND u.TABLE_NAME = tc.TABLE_NAME AND u.CONSTRAINT_NAME = tc.CONSTRAINT_NAME WHERE CONSTRAINT_TYPE = 'FOREIGN KEY') fk ON c.TABLE_SCHEMA = fk.TABLE_SCHEMA AND c.TABLE_NAME = fk.TABLE_NAME AND c.COLUMN_NAME = fk.COLUMN_NAME INNER JOIN INFORMATION_SCHEMA.TABLES t ON c.TABLE_SCHEMA = t.TABLE_SCHEMA AND c.TABLE_NAME = t.TABLE_NAME
WHERE c.TABLE_NAME NOT IN ('EdmMetadata', '__MigrationHistory')

Foreign Key SQL:

SELECT FK.name AS FK_Table, FkCol.name AS FK_Column, PK.name AS PK_Table, PkCol.name AS PK_Column, OBJECT_NAME(f.object_id) AS Constraint_Name, SCHEMA_NAME(FK.schema_id) AS fkSchema, SCHEMA_NAME(PK.schema_id) AS pkSchema, PkCol.name AS primarykey, k.constraint_column_id AS ORDINAL_POSITION
FROM sys.objects AS PK INNER JOIN sys.foreign_keys AS f INNER JOIN sys.foreign_key_columns AS k ON k.constraint_object_id = f.object_id INNER JOIN sys.indexes AS i ON f.referenced_object_id = i.object_id AND f.key_index_id = i.index_id ON PK.object_id = f.referenced_object_id INNER JOIN sys.objects AS FK ON f.parent_object_id = FK.object_id INNER JOIN sys.columns AS PkCol ON f.referenced_object_id = PkCol.object_id AND k.referenced_column_id = PkCol.column_id INNER JOIN sys.columns AS FkCol ON f.parent_object_id = FkCol.object_id AND k.parent_column_id = FkCol.column_id
ORDER BY FK_Table, FK_Column

Extended Properties:

SELECT s.name AS [schema], t.name AS [table], c.name AS [column], value AS [property]
FROM sys.extended_properties AS ep INNER JOIN sys.tables AS t ON ep.major_id = t.object_id INNER JOIN sys.schemas AS s ON s.schema_id = t.schema_id INNER JOIN sys.columns AS c ON ep.major_id = c.object_id AND ep.minor_id = c.column_id
WHERE class = 1
ORDER BY t.name

I like this format:

name DataType Collation Constraints PK FK Comment
id int NOT NULL IDENTITY PK Order Line Id
pid int NOT NULL tbl_orders Order Id
itemCode varchar(10) Latin1_General_CI_AS NOT NULL Product Code

So I have used this:

DECLARE @tname varchar(100) = 'yourTableName';
SELECT col.name, CASE typ.name WHEN 'nvarchar' THEN 'nvarchar('+CAST((col.max_length / 2) as varchar)+')' WHEN 'varchar' THEN 'varchar('+CAST(col.max_length as varchar)+')' WHEN 'char' THEN 'char('+CAST(col.max_length as varchar)+')' WHEN 'nchar' THEN 'nchar('+CAST((col.max_length / 2) as varchar)+')' WHEN 'binary' THEN 'binary('+CAST(col.max_length as varchar)+')' WHEN 'varbinary' THEN 'varbinary('+CAST(col.max_length as varchar)+')' WHEN 'numeric' THEN 'numeric('+CAST(col.precision as varchar)+(CASE WHEN col.scale = 0 THEN '' ELSE ','+CAST(col.scale as varchar) END) +')' WHEN 'decimal' THEN 'decimal('+CAST(col.precision as varchar)+(CASE WHEN col.scale = 0 THEN '' ELSE ','+CAST(col.scale as varchar) END) +')' ELSE typ.name END DataType, ISNULL(col.collation_name,'') Collation, CASE WHEN col.is_nullable = 0 THEN 'NOT NULL ' ELSE '' END + CASE WHEN col.is_identity = 1 THEN 'IDENTITY' ELSE '' END Constraints, ISNULL((SELECT 'PK' FROM sys.key_constraints kc INNER JOIN sys.tables tb ON tb.object_id = kc.parent_object_id INNER JOIN sys.indexes si ON si.name = kc.name INNER JOIN sys.index_columns sic ON sic.index_id = si.index_id AND sic.object_id = si.object_id WHERE kc.type = 'PK' AND tb.name = @tname AND sic.column_id = col.column_id),'') PK, ISNULL((SELECT (SELECT name FROM sys.tables st WHERE st.object_id = fkc.referenced_object_id) FROM sys.foreign_key_columns fkc INNER JOIN sys.columns c ON c.column_id = fkc.parent_column_id AND fkc.parent_object_id = c.object_id INNER JOIN sys.tables t ON t.object_id = c.object_id WHERE t.name = tab.name AND c.name = col.name),'') FK, ISNULL((SELECT value FROM sys.extended_properties WHERE major_id = tab.object_id AND minor_id = col.column_id),'') Comment
FROM sys.columns col INNER JOIN sys.tables tab ON tab.object_id = col.object_id INNER JOIN sys.types typ ON typ.system_type_id = col.system_type_id
WHERE tab.name = @tname AND typ.name != 'sysname'
ORDER BY col.column_id;

1. First connect to your DB,

use DB_name

2. Then

exec sp_help 'Production.Et_Issue'

here 'production' is the schema name. If you dont have a schema, you may simply write sp_help table_name

use

SELECT COL_LENGTH('tablename', 'colname')

None of other solution worked for me.

2

SELECT C.COLUMN_NAME, C.IS_NULLABLE, C.DATA_TYPE, TC.CONSTRAINT_TYPE, C.COLUMN_DEFAULT FROM INFORMATION_SCHEMA.COLUMNS AS C FULL JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE AS CC ON C.COLUMN_NAME = CC.COLUMN_NAME FULL JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC ON CC.CONSTRAINT_NAME = TC.CONSTRAINT_NAME
WHERE C.TABLE_NAME = '<Table Name>';

Sample Output

1

If you are using FirstResponderKit from Brent Ozar team, you can run this query also:

exec sp_blitzindex @tablename='MyTable'

It will return all information about table:

• indexes with their usage statistics(reads, writes, locks, etc), space used and other
• missing indexes
• columns
• foreign keys
• statistics contents

Of course it's not a system and not so universal stp like sp_help or sp_columns, but it returns all possible information about your table and I think it's worth creating it at your environment and mentioning it here.

Just double click on the table name and press Alt+F1

CREATE PROCEDURE [dbo].[describe]
(
@SearchStr nvarchar(max)
)
AS
BEGIN
SELECT CONCAT([COLUMN_NAME],' ',[DATA_TYPE],' ',[CHARACTER_MAXIMUM_LENGTH],' ', (SELECT CASE [IS_NULLABLE] WHEN 'NO' THEN 'NOT NULL' ELSE 'NULL' END), (SELECT CASE WHEN [COLUMN_DEFAULT] IS NULL THEN '' ELSE CONCAT(' DEFAULT ',[COLUMN_DEFAULT]) END) ) AS DESCRIPTION FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME LIKE @SearchStr
END 

The query below will provide similar output as the info() function in python, Pandas library.

USE [Database_Name]
IF OBJECT_ID('tempdo.dob.#primary_key', 'U') IS NOT NULL DROP TABLE #primary_key
SELECT
CONS_T.TABLE_CATALOG,
CONS_T.TABLE_SCHEMA,
CONS_T.TABLE_NAME,
CONS_C.COLUMN_NAME,
CONS_T.CONSTRAINT_TYPE,
CONS_T.CONSTRAINT_NAME
INTO #primary_key
FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS CONS_T
JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE AS CONS_C ON CONS_C.CONSTRAINT_NAME= CONS_T.CONSTRAINT_NAME
SELECT
SMA.name AS [Schema Name],
ST.name AS [Table Name],
SC.column_id AS [Column Order],
SC.name AS [Column Name],
PKT.CONSTRAINT_TYPE,
PKT.CONSTRAINT_NAME,
SC.system_type_id,
STP.name AS [Data Type],
SC.max_length,
SC.precision,
SC.scale,
SC.is_nullable,
SC.is_masked
FROM sys.tables AS ST
JOIN sys.schemas AS SMA ON SMA.schema_id = ST.schema_id
JOIN sys.columns AS SC ON SC.object_id = ST.object_id
JOIN sys.types AS STP ON STP.system_type_id = SC.system_type_id
LEFT JOIN #primary_key AS PKT ON PKT.TABLE_SCHEMA = SMA.name AND PKT.TABLE_NAME = ST.name AND PKT.COLUMN_NAME = SC.name
ORDER BY ST.name ASC, SMA.name ASC

select * from sysobjects where name='TABLENAME'

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy