get basic SQL Server table structure information

Question

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

User · Accepted Answer

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

User · Answer

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

User · Answer

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

User · Answer

You could use these functions   sp help TableName sp helptext ProcedureName

User · Answer

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

User · Answer

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

[sql] get basic SQL Server table structure information

Examples related to sql

Examples related to sql-server