MS SQL Server Basic Queries

1. List all the database objects 

Select * from Sysobjects

The list of all of the possible values for the xtype column in the sysobjects table of a SQL Server database:

• C - CHECK constraint
• D - Default or DEFAULT constraint
• F - FOREIGN KEY constraint
• L - Log
• P - Stored procedure
• PK - PRIMARY KEY constraint
• RF - Replication filter stored procedure
• S - System table
• TR - Trigger
• U - User table
• UQ - UNIQUE constraint
• V - View
• X - Extended stored procedure

2. List all the columns on the database

Select * from syscolumns

xtype column in the syscolumns table contains the datatype information. The possible xtype values are

127 : bigint
173 :  binary
104 :  bit
175 :  char
61 :  datetime
106 :  decimal
62 :  float
34 :  image
56 :  int
60 :  money
239 :  nchar
99 :  ntext
108 :  numeric
231 :  nvarchar
59 :  real
58 :  smalldatetime
52 :  smallint
122 :  smallmoney
98 :  sql_variant
231 :  sysname
35 :  text
189 :  timestamp
48 :  tinyint
36 :  uniqueidentifier
165 :  varbinary
167 :  varchar

3. List all the tables

select * from sys.tables

4. List tables along with their columns

[code language="T-SQL"]

SELECT    
 o.name AS [Table Name],
 o.type,
 c.name AS [Col Name],
 s.name AS [Col Type],
 c.prec,
 c.scale,
 c.isnullable
FROM       
 dbo.sysobjects AS o
INNER JOIN
 dbo.syscolumns AS c
  ON
   c.id = o.id
INNER JOIN
 dbo.systypes AS s
  ON
   c.xtype = s.xtype
WHERE  (
   o.type = 'U'
)