CODEWITHSUNDEEP
sqlsql-server
1252748
1252748

Reputation: 15362

get basic SQL Server table structure information

I can get the number of columns in an SQL Server database with this:

SELECT COUNT(*)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = 'Address'

But is there any way (for an unknown number of columns) I can get the name and datatype and length of each column?

Upvotes: 31

Views: 181663

Answers (6)

Shubham Bhangale
Shubham Bhangale

Reputation: 97

You could use these functions:

sp_help TableName
sp_helptext ProcedureName

Upvotes: 0

Zafor
Zafor

Reputation: 353

Write the table name in the query editor select the name and press Alt+F1 and it will bring all the information of the table.

Upvotes: 4

satya
satya

Reputation: 1

For total columns information use below syntax : Use "DBName" go Exec SP_Columns "TableName"

For total table information use below syntax : Use "DBName" go Exec SP_help "Table Name"

Upvotes: 0

Kprof
Kprof

Reputation: 752

Name and datatype:

USE OurDatabaseName
GO

SELECT 
    sc.name AS [Columne Name], 
    st1.name AS [User Type],
    st2.name AS [Base Type]
FROM dbo.syscolumns sc
    INNER JOIN dbo.systypes st1 ON st1.xusertype = sc.xusertype
    INNER JOIN dbo.systypes st2 ON st2.xusertype = sc.xtype
-- STEP TWO: Change OurTableName to the table name
WHERE sc.id = OBJECT_ID('OurTableName')
ORDER BY sc.colid

Or:

SELECT COLUMN_NAME AS ColumnName, DATA_TYPE AS DataType, CHARACTER_MAXIMUM_LENGTH AS CharacterLength
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'OurTableName'

Upvotes: 4

Roarster
Roarster

Reputation: 804

sp_help will give you a whole bunch of information about a table including the columns, keys and constraints. For example, running 

exec sp_help 'Address'

will give you information about Address.

Upvotes: 32

Taryn
Taryn

Reputation: 247620

Instead of using count(*) you can SELECT * and you will return all of the details that you want including data_type:

SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = 'Address'

MSDN Docs on INFORMATION_SCHEMA.COLUMNS

Upvotes: 69

Related Questions