StumbleUpon
Share on Facebook

Wednesday, May 9, 2007

No of Records using Select Query

-- No of Total Records using Select Query
Select count(*) from Purchasing.PurchaseOrderDetail

-- No of Records for each group using Select Query
Select PurchaseOrderID, count(*) NoOfRec from Purchasing.PurchaseOrderDetail Group By PurchaseOrderID
StumbleUpon
Share on Facebook

Identify Column in All Tables

use AdventureWorks
Select * From sys.columns
select * from sys.tables

-- Select Columns From All Tables
select st.name, sc.name from sys.tables st, sys.columns sc
where st.object_id = sc.object_id

-- Select Specific Columns and their corresponding tables
select st.name, sc.name from sys.tables st, sys.columns sc
where st.object_id = sc.object_id
and sc.name = 'EmployeeID'

-- Select Specific Columns and their corresponding tables
select st.name, sc.name from sys.tables st, sys.columns sc
where st.object_id = sc.object_id
and sc.name = 'PurchaseOrderID'
StumbleUpon
Share on Facebook

Monday, May 7, 2007

Write Log Files - Sybase

Writing Logs - Text Files using Sybase

If you want to write to a Log file using Sybase use the following
code. Here the log file is not overwritten - it is appended with the
existing text. As the existing text is stored in a variable and
rewritten to the file there will be a gradual slowdown in performance

BEGIN
Declare sbuffer long varchar;
Declare sText long varchar;
Declare iRet integer;
Set sbuffer = xp_read_file('c:\temp\sample.sql');
Set sText = '\n';
Set sText = sText || 'Writing Text @ ' || Now(*);
Set iRet = xp_write_file('c:\temp\sample.sql', sbuffer || sText);
END;
StumbleUpon
Share on Facebook

Link Triggers & Tables

Triggers and thier corresponding Tables

-- Sybase
select * from systrigger

-- SQL Server
select * from sys.triggers

-- Triggers and Tables
If you want to know the triggers for each table or trigger for a
corresponding table use the following:

-- Sybase
Select stab.table_name, strig.trigger_name from systable stab, systrigger strig
where stab.table_id = strig.table_id

-- SQL Server
Select stab.name, strig.name from sys.tables stab, sys.triggers strig
where stab.object_id = strig.parent_id
StumbleUpon
Share on Facebook

Stored Procedures & Parameters

SQL Server / Sybase Stored Procedures & Parameters

-- Sybase Syntax to Select Parameters used in All Available Stored Procedures
select * from SYSPROCPARM

-- SQL Server Syntax to Select Parameters used in All Available Stored Procedures
select * from sys.parameters


-- Sybase Syntax to Select All Available Stored Procedures
select * from sysprocedure

-- SQL Server Syntax to Select All Available Stored Procedures
select * from sys.procedures


-- To get the list of both input and output parameters used in every
stored procedure use the following syntax

-- Sybase
select sp.proc_name, spa.parm_name from sysprocedure sp, SYSPROCPARM spa
where sp.proc_id = spa.proc_id
order by sp.proc_name

--SQL Server
select sp.name, spa.name from sys.procedures sp, sys.parameters spa
where sp.object_id = spa.object_id
order by sp.name


-- Procedures and their Input Parameters

-- Sybase
select sp.proc_name, spa.parm_name from sysprocedure sp, SYSPROCPARM spa
where sp.proc_id = spa.proc_id
and parm_mode_in = 'Y'
order by sp.proc_name


-- SQL Server
select sp.name, spa.name from sys.procedures sp, sys.parameters spa
where sp.object_id = spa.object_id
and spa.is_output = 0
order by sp.name



-- Procedures and their Output Parameters
-- Sybase
select sp.proc_name, spa.parm_name from sysprocedure sp, SYSPROCPARM spa
where sp.proc_id = spa.proc_id
and parm_mode_out = 'Y'
order by sp.proc_name

-- SQL Server
select sp.name, spa.name from sys.procedures sp, sys.parameters spa
where sp.object_id = spa.object_id
and spa.is_output = 1
order by sp.name
Related Posts Plugin for WordPress, Blogger...