Microsoft SQL Server/Tables

Variables

Procédures stockées

Microsoft SQL Server

Annexes

Version imprimable

[ Modifier le sommaire ]

Variables

Procédures stockées

Introduction

Les langages de définition et de manipulation de données (LDD et LMD) respectent la norme SQL-86. Toutefois, en plus des requêtes SELECT, UPDATE, INSERT on trouve MERGE depuis la version 2008^[1].

Créer une table

Dans SSMS, un clic droit sur le dossier "Tables" d'une base permet d'en ajouter.

Sinon en SQL il faut taper^[2] :

CREATE TABLE [dbo].[table1] (
	[Nom] [varchar](250) NULL,
	[Prénom] [varchar](250) NULL,
	[identifiant] [int] IDENTITY(1,1) NOT NULL)

Un clic droit sur une table existante permet au choix de :

Modifier sa structure (ajouter une colonne, modifier un type).
Sélectionner ses 1 000 premiers enregistrements.
Éditer ses 200 premiers.

Pour sélectionner d'autre fractions de la table, utiliser TOP :

SELECT TOP 100 * FROM table1                  -- Les 100 premiers
SELECT TOP 100 * FROM table1 ORDER BY id DESC -- Les 100 derniers
SELECT TOP (10) PERCENT * FROM table1         -- Les 10 premiers pourcents

Collation

On peut préciser la collation des champs avec le mot COLLATE^[3]. Par exemple pour que le "é" soit classé entre le "e" et le "f" (et pas après le "z") :

CREATE TABLE [dbo].[table2] (
	[Nom] [varchar](250) COLLATE French_CI_AS NOT NULL)

Remplir une table

Remplissage des premières colonnes^[4] :

INSERT INTO table1 VALUES ('Doe', 'Jane', 1), ('Doe', 'John', 2)

Pour certaines colonnes ciblées il faut préciser les champs. Par exemple en ne remplissant que le prénom, le nom de famille sera nul :

INSERT INTO table1 (Prénom, identifiant) VALUES ('Jane', 3)

Depuis une autre table :

INSERT INTO table1 (Prénom, identifiant)
SELECT Prénom, ID FROM table2

Mise à jour :

UPDATE table1
SET Prénom = 'Janet'
WHERE ID = 3

UPDATE table1
SET Prénom = t2.Prénom, Nom = t2.Nom
FROM table1 t1
INNER JOIN table2 t2 on t1.ID = t2.ID_t1

Créer un index

L'abréviation PK du logiciel signifie "primary key" (clé primaire).

Pour créer une clé étrangère, dérouler la table, dans le menu Clés, clic droit, nouvelle clé étrangère..., la liste de toutes les clés étrangères de la table apparait dans une petite fenêtre (baptisées par défaut "FK_..." pour "foreign key" : clé étrangère).

Dans Général, Spécification de tables et colonnes, cliquer sur "..." pour sélectionner la table et son champ à lier.

Si ensuite l'erreur suivante survient :

Les colonnes de la table ne correspondent pas à une clé primaire existante ou à une contrainte UNIQUE

Il faut définir une contrainte d'unicité^[5] sur les valeurs du champ. Ainsi toutes ses valeurs seront différentes (sauf les null) et l'utilisateur sera averti d'une erreur s'il tente d'insérer un doublon.

Ajouter un identifiant unique

Normalement chaque table doit posséder au moins un identifiant unique (clé primaire). Or, il est impossible de modifier une colonne existante pour lui attribuer la propriété AUTOINCREMENT nécessaire à une telle clé.

Il faut donc en ajouter une :

ALTER TABLE table1 ADD id int NOT NULL IDENTITY (1,1) PRIMARY KEY

Copier une table

La sélection ci-dessous clone une table avec les mêmes tailles de champs :

SELECT * INTO table2 FROM table1

Sachant que la table spt_values de la base système master fournit déjà des numéros séquentiels via son champ number, il devient possible de générer des tables préremplies avec le compteur :

SELECT DISTINCT number 
FROM master.dbo.spt_values
WHERE number BETWEEN 2 AND 10

D'où :

SELECT DISTINCT 'Ligne ' + convert(varchar, number, 112) as No into #TableVierge
FROM master.dbo.spt_values
WHERE number BETWEEN 2 AND 10

SELECT * from #TableVierge

No
Ligne 10
Ligne 2
Ligne 3
Ligne 4
Ligne 5
Ligne 6
Ligne 7
Ligne 8
Ligne 9

Importer une table

Soit un tableau (Excel ou Calc) converti par exemple en CSV encodé en PC DOS, pour l'importer en tant que nouvelle table^[6] :

CREATE TABLE Tableau_to_Table (
  [Champ1] [varchar](500) NULL,
  [Champ2] [varchar](500) NULL,
  [Champ3] [varchar](500) NULL
)
GO
BULK INSERT Tableau_to_Table
FROM 'C:\Users\superadmin\Desktop\Tableau1.csv'
WITH (
  FIELDTERMINATOR = ';',
  ROWTERMINATOR = '\n'
)
GO
-- Affiche le résultat
SELECT * from Tableau_to_Table
GO

Si la table possède un ID en autoincrémentation, le contenu du CSV s'ajoutera à la suite. Pour éviter cela, utiliser DBCC CHECKIDENT^[7].

Supprimer une table

Pour supprimer toute la table (données et structure) :

DROP TABLE table1

Pour tronquer une table, c'est-à-dire ne conserver que les en-têtes et types des colonnes, en retirant tous les enregistrements :

TRUNCATE TABLE table1
--ou
DELETE table1

Pour supprimer certaines lignes d'une table :

DELETE table1 WHERE Condition

En ajoutant OUTPUT deleted.* avant le WHERE, on obtient le contenu supprimé au lieu du nombre de lignes supprimées.

Rechercher une table

Pour rechercher une table dont on connait le nom exact, dans toutes les bases de données du serveur :

sp_MSforeachdb 'USE ?
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N''[MaTableConnue]'') AND OBJECTPROPERTY(id, N''IsUserTable'') = 1)
BEGIN
  PRINT ''Table trouvée dans la base : ?''
END'

Rechercher dans toutes les tables

SSMS 10 ne propose pas de fonction de recherche de table, champ ou valeur, comme on en trouve dans phpMyAdmin pour MySQL par exemple.

Recherche d'une table

Ce script parcourt chaque base de données pour y récupérer les tables dont les noms contiennent une chaine de caractères spécifiée (à la fin).

ALTER Proc FindTable
@TableName nVarchar(50)
As
/*
Purpose : Search for a Table in all databases
Author : Sandesh Segu
Date : 17th July 2009
Version : 1.0
More Scripts  : http://sanssql.blogspot.com
*/
ALTER Table #temp (DatabaseName varchar(50),SchemaName varchar(50),TableName varchar(50))

Declare @SQL Varchar(500)
Set @SQL='Use [?] ;
if exists(Select name from sys.tables where name like '''+@TableName+''') 
insert into #temp 
Select ''?'' AS DatabaseName ,SS.Name AS SchemaName ,ST.Name AS TableName from sys.tables as ST , sys.schemas SS 
where ST.Schema_ID=SS.Schema_ID and ST.name like '''+@TableName+''''

EXEC sp_msforeachdb @SQL

Select * from #temp

Drop table #temp
GO

EXEC FindTable '%Chaine à rechercher%'

Recherche d'une valeur

La recherche d'une valeur de champ dans toutes les tables prend un certain temps^[8] :

CREATE TABLE #result(
  id      INT IDENTITY,
  tblName VARCHAR(255),
  colName VARCHAR(255),
  qtRows  INT
)
go

DECLARE @toLookFor VARCHAR(255)
SET @toLookFor = '%Chaine à rechercher%'

DECLARE cCursor CURSOR LOCAL FAST_FORWARD FOR
SELECT
  '[' + usr.name + '].[' + tbl.name + ']' AS tblName,
  '[' + col.name + ']' AS colName,
  LOWER(typ.name) AS typName
FROM
  sysobjects tbl
    INNER JOIN(
      syscolumns col
        INNER JOIN systypes typ
        ON typ.xtype = col.xtype
    )
    ON col.id = tbl.id
    --
    LEFT OUTER JOIN sysusers usr
    ON usr.uid = tbl.uid

WHERE tbl.xtype = 'U'
  AND LOWER(typ.name) IN(
        'char', 'nchar',
        'varchar', 'nvarchar',
        'text', 'ntext'
      )
ORDER BY tbl.name, col.colorder
--
DECLARE @tblName VARCHAR(255)
DECLARE @colName VARCHAR(255)
DECLARE @typName VARCHAR(255)

DECLARE @sql  NVARCHAR(4000)
DECLARE @crlf CHAR(2)

SET @crlf = CHAR(13) + CHAR(10)

OPEN cCursor
FETCH cCursor
INTO @tblName, @colName, @typName

WHILE @@fetch_status = 0
BEGIN
  IF @typName IN('text', 'ntext')
  BEGIN
    SET @sql = ''
    SET @sql = @sql + 'INSERT INTO #result(tblName, colName, qtRows)' + @crlf
    SET @sql = @sql + 'SELECT @tblName, @colName, COUNT(*)' + @crlf
    SET @sql = @sql + 'FROM ' + @tblName + @crlf
    SET @sql = @sql + 'WHERE PATINDEX(''%'' + @toLookFor + ''%'', ' + @colName + ') > 0' + @crlf
  END
  ELSE
  BEGIN
    SET @sql = ''
    SET @sql = @sql + 'INSERT INTO #result(tblName, colName, qtRows)' + @crlf
    SET @sql = @sql + 'SELECT @tblName, @colName, COUNT(*)' + @crlf
    SET @sql = @sql + 'FROM ' + @tblName + @crlf
    SET @sql = @sql + 'WHERE ' + @colName + ' LIKE ''%'' + @toLookFor + ''%''' + @crlf
  END

  EXECUTE sp_executesql
            @sql,
            N'@tblName varchar(255), @colName varchar(255), @toLookFor varchar(255)',
            @tblName, @colName, @toLookFor

  FETCH cCursor
  INTO @tblName, @colName, @typName
END

SELECT *
FROM #result
WHERE qtRows > 0
ORDER BY id
GO

DROP TABLE #result
go

Pour rechercher dans toutes les vues, remplacer tbl.xtype = 'U' par tbl.xtype = 'V'^[9].

Créer une vue

Pour créer une vue :

CREATE VIEW vue1
AS
SELECT DISTINCT champ1
FROM table1;

dans SSMS, l'affichage d'une telle vue n'est pas plus rapide que la requête l'ayant générée, sauf si elle a un index cluster^[10].

Dans ce cas, la différence de temps d'affichage est très significative :

CREATE VIEW vue1 WITH SCHEMABINDING
AS
SELECT COUNT_BIG(*) as Compte, champ1
FROM dbo.table1
group by champ1;

SELECT champ1 FROM vue1 WITH (NOEXPAND);

Retrouver la requête générant une vue

Dans l'interface graphique, clic droit dessus, Générer un script de la vue en tant que, CREATE TO.

En lignes de commande :

EXEC sp_helptext ma_vue;

Jointures

inner join : jointure interne
left join : jointure à gauche
right join : jointure à droite
full outer join : jointure externe
apply^[11] : intra-jointure
1. cross apply
2. outer apply

Références

[1] ttps://msdn.microsoft.com/fr-fr/library/bb510625%28v=sql.120%29.aspx

[2] ttps://msdn.microsoft.com/en-us/library/ms174979.aspx

[3] ttps://msdn.microsoft.com/fr-fr/library/ms190920.aspx

[4] ttps://msdn.microsoft.com/en-us/library/ms174335.aspx

[5] ttp://office.microsoft.com/fr-fr/help/les-colonnes-de-la-table-0s-ne-correspondent-pas-a-une-cle-primaire-existante-ou-a-une-contrainte-unique-HP003083867.aspx

[6] ttp://msdn.microsoft.com/fr-fr/library/ms188365.aspx

[7] ttps://msdn.microsoft.com/fr-fr/library/ms176057%28v=sql.120%29.aspx?f=255&MSPPError=-2147217396

[8] ttp://stackoverflow.com/questions/591853/search-for-a-string-in-an-all-the-tables-rows-and-columns-of-a-db

[9] ttps://docs.microsoft.com/fr-fr/sql/relational-databases/system-compatibility-views/sys-sysobjects-transact-sql?view=sql-server-2017

[10] ttps://technet.microsoft.com/en-us/library/dd171921%28v=sql.100%29.aspx

[11] ttps://technet.microsoft.com/fr-fr/library/ms175156%28v=sql.105%29.aspx?f=255&MSPPError=-2147217396

[1]

[2]

[3]

[4]

[5]

[6]

[7]

[8]

[9]

[10]

[11]