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?
This question is related to
sql
sql-server
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
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.
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"
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.
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'
You could use these functions:
sp_help TableName
sp_helptext ProcedureName
Source: Stackoverflow.com