SQL Related Articles: Creating views with information about tables and columsn etc
2002-12-10 -- Andy Stapleton
Newsgroups: TopSpeed.Products.Drivers
Editors note: Please not that the SQL code at the end may wrap.
Here is a few views you may find interesting...
Create view
ViewTableColumn
as select Table_Name,Column_Name from
SYS.Systable,SYS.syscolumn where
syscolumn.Table_Id = systable.table_id and
systable.creator = 11
! gives all of the Tables and Columns for a particular user that created
it...
alter view
ViewTableColumnfilter
as select st.Table_Name,sc.Column_Name,Trim(Su.Name) || '.' ||
St.Table_name as tTableNAme from
SYS.Systable as st,SYS.syscolumn as sc,Dbo.SysUsers as su where
sc.Table_Id = st.table_id and
st.creator = 9 and
Su.Uid = St.Creator
! creates the table name with the Owner attached and the columns...
alter view
ViewTableOwner
as select su.Name,st.Table_Name,Trim(Su.Name) || '.' || St.Table_name as
tTableNAme from
dbo.sysUsers as Su,SYS.SysTable as st where
Su.Uid = St.Creator and
Su.Name not in('DBO','sys','Rs_SysTabGroup')
! shows all owners/ tables except for system stuff....
Find attached a SQL procedure, it takes a Table I created SeqTransColumns
Finds the column in the SysColumns table and creates a statement on the fly
to be executed.....
the reason for this in this example is to build an update statement on the
Fly for Prior changed fields, Each time a record is updated, I store the
fieldName / Value into the SeqTransColumns tables, if I need to Rollback
the changes later, or process it some way, I don't necessarily know the
fieldnames that was changed, by using the syscolumns table and getting info
from there, I can setup the Update statement on the fly...
--
Andy <> Stapleton
CIO DB|Wired.com / (ccs) Cowboy Computing Solutions
www.dbwired.com Web and Clarionet hosting
www.ccscowboy.com CCS SQL templates
www.Paywire.com ACH / Credit Card payment processing
"Arnor Baldvinsson" wrote in message
news:3df63ae9.19146890@news.softvelocity.com...
> Hi All,
>
> On Tue, 10 Dec 2002 18:49:16 GMT, arnor@icetips.com (Arnor
> Baldvinsson) wrote:
>
> >datatype itself or to a table containing the datatypes. Does anyone
> >know? It's of no importance to my code, I'm just curious as to how
>
> Never mind, found it in SYSDOMAIN.
>
> Best regards,
>
> Arnór Baldvinsson
> Icetips Software
> San Antonio, Texas, USA
> www.icetips.com
> arnor@icetips.com
> ICQ: 113314380
>
> Subscribe to information from Icetips.com:
> http://www.icetips.com/getnotificationinfo.htm
alter procedure
Benefits.OutofSequenceTmpLocations(pTrnrSysid integer,pLinksysid integer,pRevision
integer,pTassysid integer,pTrnssysid integer)
begin
declare err_notfound exception for sqlstate value '02000';
declare rFieldName varchar(60);
declare rcName varchar(255);
declare rColType varchar(255);
declare pKeyField varchar(255);
declare pTablename varchar(255);
//
declare recFound integer;
declare pSQLStatement varchar(10240);
declare rEmployeeNo varchar(25);
declare IntegerVal integer;
declare VarcharVal varchar(255);
declare DateVal date;
declare SmallintVAL smallint;
declare DecimalVal decimal(20,5);
declare testVal varchar(5);
declare sqlLength integer;
//
declare DetailCursor dynamic scroll cursor for select Fieldname,Cname,ColType from
Benefits.SeqTransColumns,SysColumns where
Trnrsysid = pTrnrsysid and
Cname = FieldName and
Creator = 'Benefits' and
Tname = pTablename and
Newrevision = pRevision;
/*
Set the values for the Primary Key field name, and Primary Table Name. this allows us to
be very generic below, but still
keep the ability to specify items directly for each table.
*/
set pKeyfield='locationsysid';
set pTableName='Locations';
//
execute immediate 'select 1 into Recfound where exists(select* from Benefits.Tmp' ||
pTableName || ' where Trnssysid = ' || pTrnssysid || ' and tassysid = ' || pTassysid || '
and ' || pKeyfield || ' = ' || pLinksysid || ')';
if Recfound is null then
execute immediate 'call Benefits.TransMove' || pTableName ||
'(NULL,null,null,pLinksysid,pTassysid,pTrnsSysid,pTRNRsysid)'
else
set pSQLStatement='Update Benefits.Tmp' || pTableName;
open Detailcursor with hold;
Execloop: loop
fetch next DetailCursor into rFieldName,rCname,rColType;
if sqlstate = Err_notFound then
leave ExecLoop
end if;
case rColType when 'Integer' then
execute immediate 'Select ' || rFieldname || ' into IntegerVal From
Benefits.ViewTrans' || pTableName || ' where revision = ' || pNewRevision || ' and ' ||
pKeyfield || ' = ' || pmembersysid;
set pSqlStatement=pSqlStatement || ' Set ' || rFieldName || ' = IntegerVal' when
'Decimal' then
execute immediate 'Select ' || rFieldname || ' into DecimalVal From
Benefits.ViewTrans' || pTableName || ' where revision = ' || pNewRevision || ' and ' ||
pKeyfield || ' = ' || pmembersysid;
set pSqlStatement=pSqlStatement || ' Set ' || rFieldName || ' = DecimalVal' when
'Smallint' then
execute immediate 'Select ' || rFieldname || ' into SmallintVal From
Benefits.ViewTrans' || pTableName || ' where revision = ' || pNewRevision || ' and ' ||
pKeyfield || ' = ' || pmembersysid;
set pSqlStatement=pSqlStatement || ' Set ' || rFieldName || ' = SmallintVal' when
'Varchar' then
execute immediate 'Select ' || rFieldname || ' into VarcharVal From
Benefits.ViewTrans' || pTableName || ' where revision = ' || pNewRevision || ' and ' ||
pKeyfield || ' = ' || pmembersysid;
set pSqlStatement=pSqlStatement || ' Set ' || rFieldName || ' = VarcharVal' when
'Char' then
execute immediate 'Select ' || rFieldname || ' into VarcharVal From
Benefits.ViewTrans' || pTableName || ' where revision = ' || pNewRevision || ' and ' ||
pKeyfield || ' = ' || pmembersysid;
set pSqlStatement=pSqlStatement || ' Set ' || rFieldName || ' = VarcharVal' when
'Date' then
execute immediate 'Select ' || rFieldname || ' into DateVal From
Benefits.ViewTrans' || pTableName || ' where revision = ' || pNewRevision || ' and ' ||
pKeyfield || ' = ' || pmembersysid;
set pSqlStatement=pSqlStatement || ' Set ' || rFieldName || ' = DateVal'
end case
;
select SUBSTRING(pSqlStatement,-1,-3) into TestVal;
if Testval = 'VAL' then
set pSqlStatement=pSqlStatement || ','
end if
end loop Execloop;
select SUBSTRING(pSqlStatement,-1,-4) into TestVal;
if Testval = 'VAL,' then
set SQLLength=LENGTH(pSQLStatement); /* get the total length of the sql statement*/
set SQLLength=SQLlength-1; /* subtract 1 so we can get rid of the remaining comma*/
set pSQLStatement=SUBSTRING(pSqlStatement,1,sqlLength) || ' where ' || pKeyField ||
' = ' || pLinksysid || ' and Tassysid = ' || pTassysid || ' and trnssysid = ' ||
pTrnssysid;
/* now that we have built the statement, execute it..*/
execute immediate pSQLstatement
end if
end if
end
Printed May 3, 2024, 10:44 pm
This article has been viewed/printed 35113 times.
Google search
has resulted in 83 hits on this article since January 25, 2004.
|