SYSOBJECTs-List All Database Objects in SQL Server

SYSOBJECTs-List All Database Objects in SQL Server

Below statemnent will list all tables int he current database.

USE YourDBName

GO

1) SELECT * FROM sys.Tables

2) SELECT 

 
 

* FROM  information_schema.Tables

Also, sysobjects, syscolumns and systypes are the database objects that we would be using here.

Sysobjects contains information about each object in the database. this includes the object name, the user id of the user who created this object and many other useful information.

      select * from sysobjects;

The most useful column in this table is the type column. This column signifies what type of object is in the returned rowset. specifying

      select * from sysobjects where type = 'u' ;

will return the list of tables on the current database. Here's a list of possible values for xtype:

• C : CHECK constraint
  
• D : Default or DEFAULT constraint
  
• F : FOREIGN KEY constraint
  
• L : Log
  
• P : Stored procedure
  
• PK : PRIMARY KEY constraint (type is K)
  
• RF : Replication filter stored procedure
  
• S : System tables
  
• TR : Triggers
  
• U : User table
  
• UQ : UNIQUE constraint (type is K)
  
• V : Views
  
• X : Extended stored procedure
  
• TF : Functions
  

You can use syscolumns  to retrieve columns on the database. doing

      select * from syscolumns

would return a result containing column information which you can use to determine the scale, data type, precision and etc. The xtype on the syscolumns table acts differently from the sysobjects. the xtype column here represents the datatatype of that column. running this script:

   select * from syscolumns where xtype = 167;

will return all columns that has a datatype of varchar. heres a list of possible values for this xtype column:

34 image

35 text

36 uniqueidentifier

48 tinyint

52 smallint

56 int

58 smalldatetime

59 real

60 money

61 datetime

62 float

98 sql_variant

99 ntext

104 bit

106 decimal

108 numeric

122 smallmoney

127 bigint

165 varbinary

167 varchar

173 binary

175 char

189 timestamp

231 nvarchar

239 nchar

241 xml

231 sysname

 
 

I got this xtypes by running : select xtype, name from systypes; which basically contains a list of available sql datatypes.