To get table details

Question

I want to get all table names and fields in that table from a particular database. Please help me to solve this.

Answer 1

Try looking at the sys.objects and sys.columns tables:

SELECT * FROM SYS.OBJECTS
WHERE TYPE = 'U'

Would give you all of the tables in that database (Type U)

SELECT 'Table name : ' +  so.name, ' Column Name: ' + sc.name FROM SYS.OBJECTS so
INNER JOIN sys.columns sc ON sc.OBJECT_ID = so.OBJECT_ID
WHERE TYPE = 'U'

Would give you all of the tables in that database and the column names. You could filter on these queries and do WHERE so.name = 'Your Table'

http://msdn.microsoft.com/en-us/library/ms190324.aspx

Answer 2

use the syntax :-sp_help your table name

like this

sp_help Payroll_Shift