Tags
information_schema.Tables, procedures, SQL Server, sys.Tables, syscolumns, sysobjects, systypes, tables, views
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.