I have been working more and more with data recently and its a painstaking task. Alot of my time includes analysis of trades and financial information to create and calculate error free result sets.
This also includes data cleanup..
My task today was to create positional and transactional data for investments. Since the company that i am working for right now is thinking of consolidating the data into one system instead of two(theres two system that basically handles the same thing).This involves digging into one of the biggest database that i have seen and finding out to properly extract data without any idea on how they structured their database (the 3rd party vendor is selling us services to do this but the company declined since it would cost alot of money.)
So here i am digging to every column, every table, every function and every view. I realized that it would be pretty hard to manually go to every database to discover what columns it contain(since there was no db diagram provided by the vendor).
The programmer inside me thought of using SQL SMO/DMO since this would just require me tho modify one of my previous demo regarding this subject.
but... i decided not too since this would be just a one time thing and can be accomplished using sql scripts and just pasting the resultset to Excel so that i could have a suitable documentation for the database structure.
One thing to remember is that sysobjects, syscolumns and systypes are your friends. This are the database objects that we would be using for this demo.
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 xtype column. This column signifies what type of object is in the returned rowset. specifying
select * from sysobjects where xtype = '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:
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
I got this xtypes by running : select xtype, name from systypes; which basically contains a list of available sql datatypes.
Putting it all together: So my idea behind this article was to create a list of available tables, and views with their underlying columns. The idea is that i can just join the sysobjects to the syscolumns to get the the tables columns and the link it to systypes to get the column datatype. The complete scripts are listed below as reference to how i was able to accomplish this task.
-----------------------------------------
-- List only the tables in this database
-----------------------------------------
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'
)
-----------------------------------------
-- List only the views in this database
-----------------------------------------
SELECT
o.name AS [View 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 = 'V'
)
-----------------------------------------
-- List only the functions in this database
-----------------------------------------
SELECT
o.name AS [Funtion 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 = 'TF'
)
hmm.. now i need to go back to work :) have fun!