SelectEtoile : Base de connaissance SGBD

Posts Tagged 'fonction'

sqsh -- note

Configuration

Couleur:

\set banner=false
\set hist_auto_save=1
\set prompt='[$histnum]{0;32}${DSQUERY}{0;45}.${database}.{0;37}${lineno}# '
#\set prompt='[$histnum]`echo ${DSQUERY} | grep PRD >/dev/null && tput setaf 1`${DSQUERY}.${database}.${lineno}#`tput sgr0` '
\if [ "`echo ${DSQUERY} | grep -c PRD`" -ne 0 ]
        \set prompt='[$histnum]{1;31}${DSQUERY}{0;37}.${database}.${lineno}# '
\else
        \set prompt='[$histnum]{0;32}${DSQUERY}{1;45}.${database}.{0;37}${lineno}#'
\fi

\set keyword_completion=smart

\alias vim='\buf-edit'
\alias tvi='\buf-edit'
\alias vi='\buf-edit -r'
\alias h='\history'

# Stats

# aliases for T-SQL commands
\alias statson='\loop -e "set statistics io on set statistics time on"'
\alias bo='\loop -e "set showplan on set statistics io on set statistics time on set plan optgoal allrows_dss"'
\alias statsoff='\loop -e "set statistics io off set statistics time off"'
\alias allstat='\loop -e "set statistics io on set statistics time on set showplan on set fmtonly on set statistics plancost on"'
\alias nostat='\loop -e "set statistics io off set statistics time off set showplan off set fmtonly off set statistics plancost off"'
\alias planon='\loop -e "set showplan on"'
\alias planoff='\loop -e "set showplan off"'
\alias ton='\loop -e "dbcc traceon(3604)"'
\alias toff='\loop -e "dbcc traceoff(3604)"'
\alias tmp='\loop -e use tempdb'
\alias mas='\loop -e "use master"'
\alias ssp='\loop -e "use sybsystemprocs"'

 

Commande

Traiter le retour d'un select ligne par ligne:

\set expand=1 (peut-être ajouter dans le .sqshrc)

select name from syslogins where status&2=2

\do

sp_locklogin #1,'unlock'

go

\done

Fonction:

Taille des bases:

\func -x \dfd
declare @pagesize numeric(12)
select @pagesize=(select @@maxpagesize)
SELECT "Database Name" = CONVERT(varchar(50), db_name(D.dbid)),
"Data Size" = STR(SUM(CASE WHEN U.segmap != 4 THEN U.size*@pagesize/1048576 END),10,1),
"Used Data" = STR(SUM(CASE WHEN U.segmap != 4 THEN size - curunreservedpgs(U.dbid, U.lstart, U.unreservedpgs)END)*@pagesize/1048576,10,1),
"Data Full%" = STR(100 * (1 - 1.0 * SUM(CASE WHEN U.segmap != 4 THEN curunreservedpgs(U.dbid, U.lstart, U.unreservedpgs) END)/SUM(CASE WHEN U.segmap != 4 THEN U.size END)),9,1) + "%",
"Log Size" = STR(SUM(CASE WHEN U.segmap = 4 THEN U.size*@pagesize/1048576 END),10,1),
"Free Log" = STR(lct_admin("logsegment_freepages",D.dbid)*@pagesize/1048576,10,1),
"Log Full%" = STR(100 * (1 - 1.0 * lct_admin("logsegment_freepages",D.dbid) /
SUM(CASE WHEN U.segmap = 4 THEN U.size END)),8,1) + "%"
FROM master..sysdatabases D,
master..sysusages U
WHERE U.dbid = D.dbid
AND ((D.dbid > 3) AND (D.dbid < 31513) AND (D.status != 256))
GROUP BY D.dbid
ORDER BY db_name(D.dbid)
go
\done

DDLGEN

\func -x \genddl

\if [ $# -ne 1 ]

\return

\fi

set nocount on

 

declare @objname varchar(30)

select @objname = "${1}"

declare @alwdup varchar(68),

@buffer varchar(255),

@count int,

@count2 int,

@dup varchar(68),

@duprow varchar(68),

@i2 int,

@indid int,

@last int,

@max2 int,

@msg varchar(250),

@name varchar(25),

@objid int,

@segment varchar(68),

@thatkey varchar(30),

@type varchar(68),

@uname varchar(20),

@uname2 varchar(20),

@unique varchar(68),

@with varchar(68),

@year char(4)

 

set nocount on

 

 

/*

**  Make sure the @objname is local to the current database.

*/

if ( @objname like "%.%.%" and

substring(@objname, 1, charindex(".", @objname) - 1) != db_name() )

begin

/* 17460, "Object must be in the current database." */

exec sp_getmessage 17460, @msg out

print @msg

goto fin

end

 

/*

**  Now check to see if the @objname is in sysobjects.  It has to be either

**  in sysobjects or systypes.

*/

select @objid = id

from sysobjects

where id = object_id(@objname)

 

/*

**  It wasn't in sysobjects so we'll check in systypes.

*/

if ( @objid = NULL )

begin

print "Object not in this database"

goto fin

end

 

 

select @count = 0,

@year=convert(char(4),datepart(year,getdate()))

 

/* Decide whether we need to SETUSER to the owner of this table */

select @uname = user_name(uid) from sysobjects where name = @objname

if ( @uname != @uname2 )

begin

select @buffer = "setuser '" + user_name(uid) + "'" from sysobjects

where name = @objname

print @buffer

print "go"

end

select @uname2 = @uname

 

select

@buffer='/*~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~'

print @buffer

select @buffer=space(7)+'Table Name: '+@objname

print @buffer

select @buffer=space(5)+'Date Created: '+convert(char(12),getdate(),107)

print @buffer

select

@buffer='~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~*/'

print @buffer

print " "

 

/* Start the table creation statement */

select @buffer = "create table " + @objname

print @buffer

select @buffer = space(12) + " ("

print @buffer

select @max2 = count(*)

from syscolumns c,

systypes t

where c.id = @objid

and c.usertype *= t.usertype

 

/* Print out each column of this table */

select  @count2 = 1

while (@count2 < (@max2 ))

begin

select @buffer = space(16) + convert(char(24),c.name) +

convert(char(20),(t.name +

substring(" (" + convert(varchar(3),c.length) + ")", 1, 1 +

6 * (charindex(t.name, "varchar/varbinary"))))) +

isnull(substring ("null",1,(status & 8)*3),"not null") + ","

from syscolumns c,

systypes t

where c.id = @objid

and c.usertype *= t.usertype

and colid=@count2

print @buffer

select @count2 = @count2 + 1

end

select @buffer = space(16) + convert(char(24),c.name) +

convert(char(20),(t.name +

substring(" (" + convert(varchar(3),c.length) + ")", 1, 1 +

6 * (charindex(t.name, "varchar/varbinary"))))) +

isnull(substring ("null",1,(status & 8)*3),"not null")

from syscolumns c, systypes t where c.id = @objid

and c.usertype *= t.usertype

and colid=@count2

print @buffer

select @buffer = space(12) + " )"

print @buffer

print "go"

print ""

 

select @objid = object_id(@objname)

 

/*

**  See if the object has any indexes.

**  Since there may be more than one entry in sysindexes for the object,

**  this select will set @indid to the index id of the first index.

*/

 

select @indid = min(indid)

from sysindexes

where id = @objid

and indid > 0

and indid < 255

 

while @indid != NULL

begin

select @unique = '',

@type = '',

@dup = '',

@duprow = '',

@alwdup = '',

@segment = ''

/*

**  Determine the index type by figuring out if it's a

**  clustered or nonclustered index.

*/

 

if @indid = 1 select @type = "clustered"

if @indid > 1 select @type = "nonclustered"

 

/*

**  Now we'll check out the status bits for this index and

**  build an english description from them.

*/

 

/*

**  See if the index is unique (0x02).

*/

if exists

( select *

from master.dbo.spt_values v, sysindexes i

where i.status & v.number = v.number

and v.type = "I"

and v.number = 2

and i.id = @objid

and i.indid = @indid )

begin

select @unique = ltrim(rtrim(v.name))

from master.dbo.spt_values v, sysindexes i

where i.status & v.number = v.number

and v.type = "I"

and v.number = 2

and i.id = @objid

and i.indid = @indid

end

 

/*

**  See if the index is ignore_dupkey (0x01).

*/

if exists

( select *

from master.dbo.spt_values v,

sysindexes i

where i.status & v.number = v.number

and v.type = "I"

and v.number = 1

and i.id = @objid

and i.indid = @indid )

begin

select @dup = v.name + space(1)

from master.dbo.spt_values v,

sysindexes i

where i.status & v.number = v.number

and v.type = "I"

and v.number = 1

and i.id = @objid

and i.indid = @indid

end

 

/*

**  See if the index is ignore_dup_row (0x04).

*/

if exists

( select *

from master.dbo.spt_values v,

sysindexes i

where i.status & v.number = v.number

and v.type = "I"

and v.number = 4

and i.id = @objid

and i.indid = @indid )

begin

select @duprow = v.name + space(1)

from master.dbo.spt_values v,

sysindexes i

where i.status & v.number = v.number

and v.type = "I"

and v.number = 4

and i.id = @objid

and i.indid = @indid

end

 

/*

**  See if the index is allow_dup_row (0x40).

*/

if exists

( select *

from master.dbo.spt_values v,

sysindexes i

where i.status & v.number = v.number

and v.type = "I"

and v.number = 64

and i.id = @objid

and i.indid = @indid )

begin

select @alwdup = v.name + space(1)

from master.dbo.spt_values v, sysindexes i

where i.status & v.number = v.number

and v.type = "I"

and v.number = 64

and i.id = @objid

and i.indid = @indid

end

 

/*

**  Add the location of the data.

*/

select @segment = s.name

from syssegments s,

sysindexes i

where s.segment = i.segment

and i.id = @objid

and i.indid = @indid

 

if ( @segment = 'default' )

begin

select @segment = NULL

end

else

begin

select @segment = @segment + space(1)

end

 

select @name = name

from sysindexes

where id = @objid

and indid = @indid

 

if ( @dup != '' )

begin

if ( datalength(@with) > 0 ) select @with = @with + ", "+@dup

else select @with = @with + @dup

end

 

if ( @duprow != '' )

begin

if ( datalength(@with) > 0 ) select @with = @with + ", "+@duprow

else select @with = @with + @duprow

end

 

if ( @alwdup != '' )

begin

if ( datalength(@with) > 0 ) select @with = @with + ", "+@alwdup

else select @with = @with + @alwdup

end

if ( @with != '' and datalength(@with) > 0 )

select @with = "with " + @with

 

if ( @unique = '' )

select @unique = @type

else

select @unique = @unique + space(1) + @type

 

print "create %1! index %2! on %3!",@unique,@name,@objname

 

/*

**  First we'll figure out what the keys are.

*/

 

select @i2 = 1,

@buffer = NULL

 

while ( @i2 <= 16 )

begin

select @thatkey = index_col(@objname, @indid, @i2)

if ( @thatkey = NULL ) goto itsdone

if ( @buffer != NULL )

begin

select @buffer = @buffer + ", "

print @buffer

end

else

begin

select @buffer = space(22) + "( "

print @buffer

end

select @buffer = space(24) + @thatkey

/*

**  Increment @i2 so it will check for the next key field.

*/

select @i2 = @i2 + 1

end

 

itsdone:

print @buffer

select @buffer = space(22)+ ")"

print @buffer

if ( @with != null ) print "%1!", @with

if ( @segment != NULL ) print "on %1!", @segment

print "go"

print " "

 

/*

**  Now move @indid to the next index.

*/

select @last = @indid

select @indid = NULL

select @indid = min(indid)

from sysindexes

where id = @objid

and indid > @last

and indid < 255

end

fin:

go

Articles tagged

Les Fonctions MySql

 

 

Fonctions sur les dates :

 

SELECT nom, naissance, CURRENT_DATE,

-> (YEAR(CURRENT_DATE)-YEAR(naissance))

-> - (RIGHT(CURRENT_DATE,5)<RIGHT(naissance,5))

-> AS age

-> FROM animal;

SELECT nom, naissance, MONTH(naissance) FROM animal;

 

 

Fonctions REGEXP :

 

SELECT * FROM animal WHERE nom REGEXP "fy$"; # colonne finissant par fy

SELECT * FROM animal WHERE nom REGEXP "^.....$";

LIKE : comme sybase

 

 

Utiliser AUTO_INCREMENT (equivalent identity)

CREATE TABLE animals (

id MEDIUMINT NOT NULL AUTO_INCREMENT,

name CHAR(30) NOT NULL,

PRIMARY KEY (id)

);

INSERT INTO animals (name) VALUES ("dog"),("cat"),("penguin"),

("lax"),("whale"),("ostrich");

SELECT * FROM animals;

Qui retourne :

+----+---------+

| id | name |

+----+---------+

| 1 | dog |

| 2 | cat |

| 3 | penguin |

| 4 | lax |

| 5 | whale |

| 6 | ostrich |

+----+---------+

Vous pouvez obtenir la valeur utilisée de la clef AUTO_INCREMENT avec la fonction SQL

LAST_INSERT_ID() ou la fonction d'API mysql_insert_id().

 

 

 

 

 

 

 

Petit guide des commandes

 

Les fonctions MySql :

  • select version(), current_date;
  • SELECT nom, naissance, CURRENT_DATE, (YEAR(CURRENT_DATE)-YEAR(naissance)), (RIGHT(CURRENT_DATE,5)<RIGHT(naissance,5)) AS age FROM animal;

  • select user();

 

Les Commandes SHOW :

  • show databases;
  • show columns from monDataCache;
  • show create table monDataCache;
  • show create database monitor_db;
  • show databases;
  • SHOW ERRORS [LIMIT [offset,] row_count]
  • SHOW COUNT(*) ERRORS
  • show engines;
  • show engines\G;
  • show grant for monty;
  • show innodb status;
  • show logs;
  • show privileges;
  • show processlist (ou mysqladmin process-list) : permet de voir qui est connecté à la base
  • show status; (ou mysqladmin extended-status)
  • show tables;
  • show tables status;
  • show variables
  • show variables like 'max_error_count';
  • show warnings; (suite a une requete avec des warnings)
  • SHOW COUNT(*) WARNINGS;

 

Les options :
 

  • L'attribut AUTO_INCREMENT peut être utilisé pour générer un identifiant unique pour les nouvelles lignes
  • Set autocommit=1 : utilisé pour la recréation rapide de la table

Les scripts natifs :

  • mysqlcc (MySQL Control Center) est un client interactif graphique, pour exécuter des commandes SQL, et administrer le serveur
  • mysqladmin est un client d'administration
  • mysqlcheck effectue les opérations de maintenance sur les tables
  • mysqldump et mysqlhotcopy font les sauvegardes de bases
  • mysqlimport importe des fichiers de données
  • mysqlshow affiche des informations sur les bases et les tables
  • mysql_secure_installation : permet de securiser l'installation de mysql

ex  : mysqladmin extended-status  (-r diff; -i 1 intervalle)

Les variables :

  • SELECT @@warning_count;

 

Exemples de commandes :

mysql> show create database testjp;

+----------+-------------------------------------------------------------------+

| Database | Create Database |

+----------+-------------------------------------------------------------------+

| testjp | CREATE DATABASE `testjp` /*!40100 DEFAULT CHARACTER SET latin1 */ |

+----------+-------------------------------------------------------------------+

1 row in set (0.00 sec)

 

mysql> use testjp

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

 

Database changed

mysql> show create table jp1;

+-------+--------------------------------------------------------------------------------------------------------------------+

| Table | Create Table |

+-------+--------------------------------------------------------------------------------------------------------------------+

| jp1 | CREATE TABLE `jp1` (

`a` int(11) default NULL,

`b` int(11) default NULL

) ENGINE=MyISAM DEFAULT CHARSET=latin1 |

+-------+--------------------------------------------------------------------------------------------------------------------+

1 row in set (0.00 sec)

 

mysql> SHOW GRANTS FOR fsarasy;

+-----------------------------------------------------------------------------------------------------------------+

| Grants for fsarasy@% |

+-----------------------------------------------------------------------------------------------------------------+

| GRANT ALL PRIVILEGES ON *.* TO 'fsarasy'@'%' IDENTIFIED BY PASSWORD '*63D85DCA15EAFFC58C908FD2FAE50CCBC60C4EA2' |

+-----------------------------------------------------------------------------------------------------------------+

1 row in set (0.00 sec)

 

 

 

 

 

 

 

 

SQL Server et les partitions

Vous trouverez ici quelques scripts sql permettant de visualiser et manipuler les partitions sous SQLServer

Les tables systèmes :

  • sys.tables
  • sys.indexes
  • sys.partitions
  • sys.partition_schemes
  • sys.partition_functions
  • sys.partition_parameters

 

Lister les partitions d'une base :

select object_name(object_id) as tablename,* from sys.partitions



Lister les partitions de toutes les tables

select T.name, T.Object_id, T.Type_Desc,P.Partition_Id
from sys.tables T
inner join sys.partitions P on T.object_id = P.object_id

 

Affiche la fonction et le schéma de partition de toutes les tables :

SELECT
    tbl.name AS [Name],
    tbl.object_id AS [ID],
    ISNULL( ( select sum (spart.rows) from sys.partitions spart where spart.object_id = tbl.object_id and spart.index_id < 2), 0) AS [RowCount],
    CASE WHEN 'FG'=dsidx.type THEN dsidx.name ELSE N'' END AS [FileGroup],
    CAST(CASE WHEN 'PS'=dsidx.type THEN 1 ELSE 0 END AS bit) AS [IsPartitioned],
    ps.name AS [PartitionScheme],
    pf.name    AS [PartitionFunction]
FROM
    sys.tables AS tbl
    INNER JOIN sys.indexes AS idx ON idx.object_id = tbl.object_id and idx.index_id < 2
    LEFT OUTER JOIN sys.data_spaces AS dsidx ON dsidx.data_space_id = idx.data_space_id
    LEFT OUTER JOIN sys.partition_schemes AS ps ON dsidx.name = ps.name    
    LEFT OUTER JOIN sys.partition_functions pf on ps.function_id=pf.function_id



Lister les caractéristiques des partitions d'une table

select object_name(object_id) as table_name, index_id, partition_number, rows
from sys.partitions  where object_name(object_id) = 'matable'

 

Afficher le détails des fonctions et schémas de partitions

-- Affiche les schémas et fonctions de partitions

select s.name as scheme_name, f.name as function_name, limit = case f.boundary_value_on_right when 0 then 'LEFT' else 'RIGHT' END
from sys.partition_schemes s
inner join sys.partition_functions f on s.function_id=f.function_id

 

-- Liste des paramètres et des valeurs de la fonction pfDate

select    *
from    sys.partition_functions pf
        inner join sys.partition_parameters pp on pf.function_id=pp.function_id
        where pf.name = 'pfDate'

select    *
from    sys.partition_functions pf
        inner join sys.partition_range_values prv on pf.function_id=prv.function_id
        where pf.name = 'pfDate'

 

--relation scheme fonction valeurs

select    s.name as scheme_name, f.name as function_name,
        limit = case f.boundary_value_on_right when 0 then 'LEFT' else 'RIGHT' END ,
        v.boundary_id, v.value
from sys.partition_schemes s
inner join sys.partition_functions f on s.function_id=f.function_id
inner join sys.partition_range_values v on v.function_id=f.function_id