Now i will work on Sybase - this is not so good but ok..
Version of db i will use is:
Adaptive Server Enterprise/12.5.0.2/EBF
And this is definitely not good.. its from 2001 :) .. a bit old.
I'm MS SQL Server fan. As a database developer I really appreciate work done by MS guys in developing SQL server tools: Profiler, Managment studio..
Also some usefull objects are commonly used in my daily work.
Lack of INFORMATION_SCHEMA views was one of first thing I noticed.
And this is my today task: INFORMATION_SCHEMA.PARAMETERS
CREATE VIEW dbo.INFORMATION_SCHEMA_PARAMETERS
AS
SELECT db_name() SPECIFIC_CATALOG,
user_name(sp.uid) SPECIFIC_SHEMA,
sp.name SPECIFIC_NAME,
colid ORDINAL_POSITION,
param.name PARAMETER_NAME,
CASE param.status2 WHEN 2 THEN 'OUT' ELSE 'IN' END PARAMETER_MODE,
t.name AS DATA_TYPE,
t.length LENGTH
FROM dbo.sysobjects AS sp
INNER JOIN syscolumns AS param ON (param.number = 1) AND (param.id=sp.id)
LEFT JOIN systypes AS t ON t.usertype = param.usertype
GO
CREATE VIEW INFORMATION_SCHEMA_TABLES
AS
SELECT
db_name() AS TABLE_CATALOG,
user_name(uid) AS TABLE_SCHEMA,
o.name AS TABLE_NAME,
CASE o.type
WHEN 'U' THEN 'BASE TABLE'
WHEN 'V' THEN 'VIEW'
END AS TABLE_TYPE
FROM sysobjects o
WHERE o.type IN ('U', 'V')
GO
CREATE VIEW INFORMATION_SCHEMA_COLUMNS
AS
SELECT db_name() TABLE_CATALOG,
user_name(o.uid) TABLE_SHEMA,
o.name TABLE_NAME,
c.name COLUMN_NAME,
colid ORDINAL_POSITION,
t.name AS DATA_TYPE,
t.length LENGTH,
CASE WHEN allownulls = 1 THEN 'YES' ELSE 'NO' END IS_NULLABLE
FROM dbo.sysobjects AS o
INNER JOIN syscolumns AS c ON (c.number = 0) AND (c.id=o.id)
LEFT JOIN systypes AS t ON t.usertype = c.usertype
GO
As you can see my version of INFORMATION_SCHEMA_PARAMETERS is pretty simpler than the MS version, but most impotent things are in ..
No comments:
Post a Comment