• Code source pour ASE
• Code source pour ASA
• Code source pour ASIQ

/* Auteur : Michael Pepper
 * Date   : 28.7.2006
 * Desc   : Retourne le DDL de suprpession et de création des indexes d'une table
 */ 
 	
-- Un curseur sur tous les indexes des tables "user" dans la base courante:
declare ixcsr cursor
for select name, id, indid from sysindexes
   where id in (select id from sysobjects where type = 'U')
go



set nocount on

declare @name varchar(32)
      , @id int
      , @indid int
      , @colid int

declare @cmd varchar(300)
      , @tab varchar(32)
      , @colname varchar(32)
      , @colorder varchar(32)

open ixcsr

fetch ixcsr into @name, @id, @indid

while @@sqlstatus = 0
begin
    select @tab = object_name(@id)

    -- la commande de drop... celle-ci est triviale!
    select @cmd = "drop index " + @tab + "." + @name
    print "%1!", @cmd


    -- on construit la commande de creation de facon iterative
    select @colid = 1

    select @cmd = "create index " + @name + " on " + @tab + "("
    while @colid <= 31
    begin
        select @colname = index_col(@tab, @indid, @colid)
             , @colorder = index_colorder(@tab, @indid, @colid)

        -- si le nom est null c'est qu'on a traite toutes les colonnes de
        -- l'indexe
        if @colname is null
            goto LAST_COLUMN

        if @colid > 1
            select @cmd = @cmd + ", "

        select @cmd =  @cmd + @colname + " " + @colorder

        select @colid = @colid + 1
    end

    LAST_COLUMN:
    select @cmd = @cmd + ")"

    print "%1!", @cmd

    fetch ixcsr into @name, @id, @indid
end

close ixcsr

deallocate cursor ixcsr
go

CREATE PROCEDURE hs_ClearHistory   (@Dte datetime )
AS  
/* Auteur : Fabien Celaia
 * Date   : 7.5.2003
 * Desc   : Supprime les anciennes valeurs de toutes les tables ayant une colonne Datetime appelée Timestamp.
 *          Très utile pour les tables générées par le Historical Serveur
 */ 
 
BEGIN 
    /* Scan de toutes les tables utilisateur ncluant un Timestamp dans la base courante */
    declare tbl_cur cursor  for 
        select o.name 
        from sysobjects o inner join syscolumns c
        on o.id = c.id
        where o.type="U"
        and c.name = "Timestamp"
 
    declare @sql varchar(100), @t varchar(30)

    open tbl_cur

    fetch tbl_cur into @t

    while @@sqlstatus <> 2
        begin
        checkpoint /* nécessaire avant chaque command delete compte tenu d la taille des tuples 
                      et de la petite taille du journal de transactions */ 
        select @sql = "delete "+ @t +" where Timestamp <= '"+convert(varchar(20), @Dte)+"'"
        execute (@sql) /* execution de la requête SQL  */
        select @sql=convert(varchar(10),@@rowcount) /* Récupération du nombre de tupels impactés */
        print "%1! lines deleted from %2!.",@sql,@t /* Affichage informationnel */
        fetch tbl_cur into @t
        end
    close tbl_cur
    deallocate cursor tbl_cur
END

create proc sp_ForeignKey (@TBL varchar(30))
as
/* Auteur : Fabien Celaia
 * Date   : 7.5.2003
 * Desc   : Détecte toutes les clés étrangères pointant sur la table passée en paramètre 
 *           et affiche les ordres DDL pour supprimer et recréer les contraintes.
 */ 
BEGIN
SELECT  object_name(r.constrid) CONST,
        object_name(r.tableid) TBL,
        c1.name COL,
        object_name(r.reftabid) TBL_REF,
        c2.name COL_REF
INTO #HIST_CONST
FROM    sysreferences r,
        syscolumns c1,
        syscolumns c2
WHERE r.keycnt=1
AND (object_name(r.reftabid)=@TBL OR object_name(r.reftabid)=@TBL)
AND c1.id=r.tableid
AND c2.id=r.reftabid
AND r.pmrydbid=r.frgndbid
AND r.frgndbid=db_id()
AND c1.colid=fokey1
AND c2.colid=refkey1

SELECT "ALTER TABLE "+TBL+" DROP CONSTRAINT "+CONST 
FROM #HIST_CONST

SELECT "ALTER TABLE "+TBL+" ADD CONSTRAINT "+CONST+" FOREIGN KEY ("+COL+") REFERENCES "+TBL_REF+ " ("+COL_REF+")"
FROM #HIST_CONST

END

create proc sp__cpu
AS  
/* Auteur : Fabien Celaia
 * Date   : 11.01.2002
 * Desc   : Affiche les processus en train de consommer des ressources
 * Param  : -
 */ 
select  convert(char(4), fid) Fam, 
	convert(char(4), spid) Spid, 
	convert(char(4), cpu) CPU,
        substring(suser_name(suid),1,15) "Users",
        substring(db_name(dbid),1,15) DB,
        convert(char(12), cmd) Command, 
	convert(char(12), program_name) Program, 
	convert(char(4), blocked) Blk
from master..sysprocesses
where cmd not like "AWAITING%" 
	and spid <> @@spid 
	and suid > 0
order by cpu desc

create proc sp__showplan 
AS  
/*
* Auteur : Fabien Celaia
* Date   : 8/03/2002
* Desc   : Affiche tous les plan d'exécution actifs
* Parm   : -
*/
BEGIN
set nocount on 

declare @i_spid integer
declare @c_spid char(5), @user varchar(30), @host varchar(30), @appl varchar(30)
declare @cmd varchar(30), @dbname varchar(30)
declare busy_cur cursor for 
	select spid
	from master..sysprocesses
	where cmd not like "AWAITING%" 
		and spid <> @@spid 
		and suid > 0

open busy_cur

fetch busy_cur into @i_spid 

while @@sqlstatus <>2
	begin
	select @c_spid = convert(char(5), @i_spid)
	select @user=suser_name(suid),
		 @dbname=db_name(dbid),
		 @host=hostname, 
		 @appl = program_name, 
		 @cmd = cmd 
		from master..sysprocesses
		where spid=@i_spid

	print "**************************************************************************"
	print "*  Plan d'exécution pour spid no %1!", @c_spid
	print "*  Login           : %1!", @user
	print "*  Application     : %1!", @appl	
	print "*  Machine     	  : %1!", @host
	print "*  Base de données : %1!", @dbname
	print "*  Commande        : %1!", @cmd
	print "**************************************************************************"
	print ""
	if exists (select 1 from master..sysprocesses where fid = @i_spid and spid <> @i_spid)
		begin
		print " Worker processes"
		print " ----------------"
		select spid, db_name(dbid) DB, status, cmd 
		from master..sysprocesses 
		where fid = @i_spid 
		and spid <> @i_spid
		print ""
		end
	else
		print "Pas de worker process"

	exec sp_showplan @i_spid, null,null,null
	fetch busy_cur into @i_spid 
	end

close busy_cur
deallocate cursor busy_cur
END

create proc sp__createDumpDevice (@dumpPath varchar(100))
AS  
/*
* Auteur : Fabien Celaia
* Date   : 11/05/2002
* Desc   : Supprime tous les dump devices existant et le recrée, par bloc de 2Go
* Parm   : @dumpPath : Répertoire racine pour les fichiers de sauvegarde
*/

begin

declare cur_db cursor for
select db_name(dbid) DBName, convert(int,sum(size/512)/2048.0)+1 NumDev
from master..sysusages
group by dbid
for read only

declare cur_dev cursor for
select name 
from master..sysdevices
where low = 0
and status = 16
and (name like "%_db_dmp_"
or name like "%_tran_dmp_")
for update


declare @num int, @tot int
declare @db varchar(30), @dumpdev varchar(255), @dumpname varchar(30)

open cur_dev
fetch cur_dev into @dumpname

while @@sqlstatus<>2
	begin
	exec sp_dropdevice @dumpname
	fetch cur_dev into @dumpname
	end

close cur_dev
deallocate cursor cur_dev

open cur_db
fetch cur_db into @db, @num
select @tot=@num

while @@sqlstatus <> 2
        begin

        while @num > 0
                begin
                select @dumpname = @db+'_db_dmp'+convert(varchar(2), @num)
                select @dumpdev=@dumpPath+@db+"_db_"+convert(varchar(2), @num)+
                	"of"+convert(varchar(2), @tot)+".dmp"
                exec sp_addumpdevice 'disk', @dumpname,@dumpdev
                select @num=@num-1
                end
        fetch cur_db into @db, @num
      select @tot=@num
        end

close cur_db
deallocate cursor cur_db

end

create proc sp__dbuser (@db varchar(30)=NULL)
AS  

/*
* Auteur : Fabien Celaia
* Date   : 3.3.2002
* Desc   : Affiche la liste des utilisateurs utilisant une  base de donnée spécifique
* Parm   : @db = Nom de la base (optionnel) 
* Return : Nombre d'utilsiateurs
*          -1 si la abse n'existe pas
*/
begin
set nocount on 

declare @i int
if @db is null
	select @db=db_name()
else
	if not exists(select name from master..sysdatabases where name = @db)
		begin
		Print"The database %1! does not exist in ASE %2!", @db,@@servername
		return -1
		end

select @db "Database"
print ""

/* Number of users */
select @i=count(spid)  from master..sysprocesses where dbid = db_id(@db) and suid > 0

if @i = 0
	begin
	print ""
	print "This database is unused"
	print""
	end
else
	begin 
	/* List of the users */
	declare @snum varchar(4)
	select @snum = convert(varchar(4), @i)
	print ""
	print "%1! user(s) found in database %2!, server %3! ",@snum, @db, @@servername
	print ""
	select spid, suser_name(suid) users , cmd, program_name from master..sysprocesses 
	where dbid = db_id(@db) and suid > 0

	/* Informational warning if the current user is in the database */
	if exists (select * from master..sysprocesses where spid=@@spid and dbid=db_id(@db))
		begin
		print ""
		print "FYI : YOU currently are one of the users of the database %1!, server %2!!", @db, @@servername
		print ""
		end
	end
return @i
end

create procedure sp__dumpdevice (@devname char(30)=NULL)
AS  
/*
* Auteur : Fabien Celaia
* Date   : 11/01/2002
* Desc   : Liste des dump devices
* Parm   : dump device spécifique (optional)
* Return : Nombre de dump devices
*/

begin
set nocount on

select "Device Name"=substring(d.name, 1,20),
 	 "Physical Name"= substring(d.phyname,1,50)
from master.dbo.sysdevices d
where d.status & 2 != 2
and   isnull(@devname,name) = name

return @@rowcount
end

create proc sp__cleanuser
AS  

/*
* Auteur : Fabien Celaia
* Date   : 11/01/2002
* Desc   : Supprime tous les utilisateurs d'une base et les recrée, pour autant qu'un login existe
*          Cette SP resynchronise les suid/uid après un dump/load d'un autre ASE
* Parm   : -
*/
begin

create table #TMP_USERS ( USERNAME varchar(30),
                          USERGROUP varchar(30))

declare @user_name varchar(30), @user_group varchar(30)

/* Fill up the temp table with the information from the existing users*/
insert  #TMP_USERS
select  s1.name, s2.name
from  sysusers s1, sysusers s2
where   s1.suid > 0 and			/* exclude the groups */
        s1.gid=s2.uid and
        s1.name != 'dbo'		/* exclude the dbo */

declare users_cur cursor for
select * from #TMP_USERS 
for read only

open users_cur

fetch users_cur into @user_name, @user_group

while @@sqlstatus <> 2
	begin
  	exec sp_dropuser @user_name
  	exec sp_adduser @user_name, @user_name, @user_group
	fetch users_cur into @user_name, @user_group
	end

close users_cur
deallocate cursor users_cur
drop table #TMP_USERS
end

create proc sp__dbsize (@db varchar(30) = NULL)
AS  

/*
* Author : Fabien Celaia
* Date   : 19.11.2002
* Desc   : Affiche la taille et le taux d'occupation des bases
* Parm   : @db = Nom de la base (optionnel) 
* Return : 0 : ok
*          1 : Aide affichée
*/
begin

if convert(char(1), @db) in ( "/" ,"-")
	begin
	print "Usage : 1) sp__dbsize"
	print "        2) sp__dbsize MyDbName"
	print "        2) sp__dbsize 'A Part Of MyDbName with wildcard(s)'"
	return 1
	end

select dbid, LogSize = sum(size/(@@pagesize/4)), LogFullPercent= convert(numeric(5,2),100 * (sum(size)
	- sum(curunreservedpgs(dbid, lstart, unreservedpgs)))/convert(numeric(10,0), sum(size)))
	into #Log 
	from master..sysusages
	where segmap = 4
	group by dbid

select dbid, DataSize = sum(size/(@@pagesize/4)), DataFullPercent= convert(numeric(5,2),100 * (sum(size)
	- sum(curunreservedpgs(dbid, lstart, unreservedpgs)))/convert(numeric(10,0), sum(size)))
	into #Data 
	from master..sysusages
	where segmap=3
	group by dbid

select dbid, MixSize = sum(size/(@@pagesize/4)), MixFullPercent= convert(numeric(5,2),100 * (sum(size)
	- sum(curunreservedpgs(dbid, lstart, unreservedpgs)))/convert(numeric(10,0), sum(size)))
	into #Mix 
	from master..sysusages
	where segmap<>4 
	and segmap&4 = 4
	group by dbid

select 	s.dbid, DB = convert(char(20), s.name), LogSize, DataSize, MixSize, DataFullPercent, 
	LogFullPercent, MixFullPercent
from master..sysdatabases s, #Log l, #Data d, #Mix m
where s.dbid *= l.dbid
and s.dbid *= d.dbid
and s.dbid *= m.dbid
and s.name like isnull(@db, s.name)
order by s.name

drop table #Data, #Log, #Mix
end

create proc sp__stat (@objectname varchar(30)=null)
AS  
/*
* Auteur : Fabien Celaia
* Date   : 08/03/2002
* Desc   : Affiche le nombre de lignes de chaque table (ou d'une table spécifique) en se basant 
*          sur les statistiques
* Parm   : @objectname (optionnel)
*/
begin
select o.type, o.name, s.rowcnt, s.pagecnt, s.delrowcnt, s.forwrowcnt, s.emptypgcnt
from sysobjects o, systabstats s
where o.id = s.id
and s.indid in (0,1)
and o.name =isnull(@objectname,o.name)
order by o.type, o.name
end

 
/*
* Auteur : Fabien Celaia
* Date   : 10/02/2003
* Desc   : Ces divers sripts vous aident à générer les index IQ corrects pour ASIQ12, 
*	   en générant un DDL depuis un serveur source ASE.
*/

drop proc sp_iqsyntax_table
drop proc sp_iqsyntax_index
drop proc sp_iqcompte
drop table IqCompte
go

 
/* Création d'une table technique.
 * Il ne s'agit pas seulement d'une table de travail car vous pouvez l'utilisez continuellement 
 * afin d'améliorer votre modèle IQ 
 */
 
create table IqCompte
   	(Tbl varchar(30) not null,	-- Table name
	Col varchar(30) not null,	-- Column name
	ColId int not null,		-- Column ID
	Tpe varchar(30) not null,	-- ASE datatype 
	Lg int null,			-- Size of the ASE datatype
	Cpt int null,			-- nr of distinct value per column
	CptTable int null,		-- nr of rows in the table
	Uni bit not null,		-- 1 if a unique constraint exist in this column
	LF bit not null,		-- LowFast Index : 1 if LF needs to be created in this column
	HG bit not null,		-- HighGroup Index : 1 if HG needs to be created in this column
	HNG bit not null,		-- HighNonGroup Index : 1 if HNG needs to be created in this column
	CMP varchar(30) null,		-- Compare Index : the name of the column to compare (must be in the same table !)
	WD bit not null,		-- WD Index : 1 if WD needs to be created in this column
	CONSTRAINT IqCompte_PK PRIMARY KEY (Tbl, Col))
go

create proc sp_iqcompte 
AS  
/*
* Auteur : Fabien Celaia
* Date   : 6/2/2003
* Desc   : Cette SP va rempir la table IqCompte. 
*	   Pour chaque colonne de chaque table, elle va calculer le nombre de valeurs distinctes et 
*          leur unicité éventuelle
*          Attention : cette requête est fortement consomatrice de temps et de ressources 
*	  	       (nombreux scans de table)
*/
begin
declare cur cursor for 
select "insert into IqCompte select '"+ o.name+"','"+ c.name+"',"+convert(varchar(4),colid)+", '"
	+ t.name+"',"+ convert(varchar(10), c.length) +", count(distinct "+c.name
	+"),count(*), 0, 0, 0, 0, NULL, 0 from "+ o.name ,
       "update IqCompte set Cpt = (select count(distinct "+c.name+"), CptTable = (select count(*)) from "+ 
       o.name+") where Tbl='"+o.name+"' AND Col='"+c.name+"'",  
	o.name, 
	c.name,
	c.colid
from 	sysobjects o, 
	syscolumns c, 
	systypes t 
where o.id=c.id 
and o.name not in ("IqCompte", "rs_lastcommit", "rs_threads")
and o.type="U"
and c.usertype = t.usertype 
and c.usertype not in (-1, 19, 20) -- avoid the TEXT/IMAGE and Extended Datatype

declare @sqli varchar(255), @sqlu varchar(255), @tbl varchar(30), @col varchar(30), @colid int

open cur
fetch cur into @sqli, @sqlu, @tbl, @col, @colid
while @@sqlstatus <> 2
	begin
	if exists (select 1 from IqCompte where Tbl = @tbl and Col = @col)
		execute (@sqlu)	-- refresh
	else
		execute (@sqli) 	-- insert
	fetch cur into @sqli, @sqlu, @tbl, @col, @colid
	end 

close cur
deallocate cursor cur

/* Add the TEXT/IMAGE and Extended Datatype */
insert into IqCompte
select o.name, 
	c.name,
      c.colid,
	t.name,
	c.length, NULL,NULL, 0, 0, 0, 0, NULL, 0
from 	sysobjects o, 
	syscolumns c, 
	systypes t 
where o.id=c.id 
and o.name not in ("IqCompte", "rs_lastcommit", "rs_threads")
and o.type="U"
and c.usertype = t.usertype 
and c.usertype in (-1, 19, 20) 
and not exists (select * from IqCompte where  Tbl = o.name and Col = c.name)

declare indidcur cursor 
	for select distinct object_name(i.id), 
		   index_col(object_name(i.id), i.indid, 1) 
	    from sysindexes i, sysobjects o 
	    where i.status & 2 = 2
	    and i.indid < 255
	    and o.id = i.id
	    and o.type="U"
	    and o.name not in ("IqCompte", "rs_lastcommit", "rs_threads")

open indidcur
fetch indidcur into @tbl, @col

while @@sqlstatus <> 2
	begin
	update IqCompte 
	set Uni = 1 
	where Tbl = @tbl
	and Col =  @col
	
	fetch indidcur into @tbl, @col
	end

close indidcur
deallocate cursor indidcur

update IqCompte set HG=1, Uni=1 where Cpt=CptTable and Cpt > 0	-- primary keys
update IqCompte set Tpe='numeric' where Tpe in ('float', 'double', 'floatn')
update IqCompte set LF = 1 where Cpt <= 1500
update IqCompte set HG = 1 where Cpt > 1500
update IqCompte set HNG = 1 where Cpt > 1500 
-- update IqCompte set WD = 1 where Tpe likke "%char%" and Lg between 8 and 32747 

end
go

create proc sp_iqsyntax_index
AS  
/*
* Auteur : Fabien Celaia
* Date   : 10/2/2003
* Desc   : Génération du DDL des indexes et joints en se basant sur IqCompte. 
*/
begin
select "create "+ case Uni when 1 then "UNIQUE" end +" LF index "+Tbl+"_"+Col+"_LF ON "+Tbl
	+"("+Col+")" LowFast_index 
	from IqCompte 
	where LF=1
	
select "create "+ case Uni when 1 then "UNIQUE" end +" HG index "+Tbl+"_"+Col+"_HG ON "+Tbl
	+"("+Col+")" HighGroup_index  
	from IqCompte 
	where HG=1
	
select "create HNG index "+Tbl+"_"+Col+"_HNG ON "+Tbl+"("+Col+")" HighNonGroup_index 
	from IqCompte 
	where HNG=1
	
select "create WD index "+Tbl+"_"+Col+"_WD ON "+Tbl+"("+Col+")" Word_Index 
	from IqCompte 
	where WD=1
	
select "create CMP index "+Tbl+"_"+Col+"_CMP ON "+Tbl+"("+Col+","+CMP+")" Compare_index 
	from IqCompte 
	where CMP is not null

SELECT "CREATE JOIN INDEX "+ object_name(r.constrid)+ " FOR "+ object_name(r.tableid) 
	+" FULL OUTER JOIN "+ object_name(r.reftabid)+ " ON "+ co1.name+" = "+co2.name Join_index
FROM   sysreferences r, syscolumns co1, syscolumns co2
where r.keycnt=1
and  r.frgndbid = r.pmrydbid
and r.tableid = co1.id
and r.reftabid = co2.id
and r.fokey1 = co1.colid
and r.refkey1 =co2.colid
end
go

create proc sp_iqsyntax_table (@Tbl varchar(30), @pw varchar(30)=NULL)
AS  

/*
* Auteur : Fabien Celaia
* Date   : 15/4/2003
* Desc   : Génération des commandes de chargement des indexes et joints en se basant sur IqCompte. 
*/
begin
declare cur cursor for  select Col, Tpe, Cpt from IqCompte where Tbl=@Tbl order by ColId
 
declare @col varchar(30), @tp varchar(30), @cp int, @DDL varchar(1000), @LOAD varchar(1000)
declare @iDDL int, @iLOAD int, @maxpacket int
declare @LOADSHORT varchar(1000), @BCP varchar(1000)

open cur
fetch cur into @col, @tp, @cp

select @DDL="CREATE TABLE "+@Tbl +"(
"
select @LOAD="LOAD TABLE "+@Tbl+"(
"

while @@sqlstatus <> 2
	begin
	select @DDL = @DDL + @col+" "+@tp
	if @cp > 0 
		select @DDL=@DDL+" IQ UNIQUE ("+convert(varchar(10), @cp)+")"
	select @DDL=@DDL+",
"

	select @LOAD = @LOAD + @col+" '~' NULL ('NULL','null'),
"
	fetch cur into @col, @tp, @cp
	end 

select @iDDL=datalength(@DDL), @iLOAD=datalength(@LOAD)
select @DDL=substring(@DDL,1,@iDDL-2)+") ;"
select @LOAD=substring(@LOAD,1,@iLOAD-26)+" '\x0a' NULL ('NULL', 'null')) from "+@Tbl
	+".bcp ESCAPES OFF QUOTES OFF WITH CHECKPOINT ON; COMMIT;"
select @LOADSHORT="INSERT INTO "+@Tbl+" LOCATION '"+@@servername+"."+db_name()+"' {select * from "+@Tbl+"};"
select @BCP="bcp "+db_name()+".."+@Tbl+" out "+@Tbl+".bcp -U"+suser_name()+" -c -t~ -S"+@@servername

select @maxpacket = cu.value 
from master..sysconfigures co  inner join master..syscurconfigs cu on co.config =cu.config
where co.comment like "max network packet size"

if @maxpacket > 512
	select @BCP=@BCP +" -A"+convert(varchar(4), @maxpacket)

if @pw is not null
	select @BCP=@BCP +" -P"+@pw

select @DDL "DDL"
select @BCP "BCP"
select @LOAD "LOAD"
union
select @LOADSHORT "LOAD"

close cur
deallocate cursor cur
end
go