SQL Server - La FAQConsultez toutes les FAQ

1
2
3
SELECT table_name 
FROM information_schema.tables 
WHERE table_type='BASE TABLE'

SELECT name FROM sysobjects WHERE type='U'

1
2
3
SELECT   COLUMN_NAME, ORDINAL_POSITION 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME='MA_TABLE'

EXEC sp_columns 'Nom_de_table'

1
2
3
4
5
6
7
8
9
SELECT c.colid, c.name Colonne, 
       t.name Type, 
       CAST(c.prec as varchar(10)) + 
           CASE WHEN c.scale > 0 THEN ',' + CAST(c.scale as varchar(10) )  
                         ELSE ''  
END Taille 
 FROM syscolumns c INNER JOIN systypes t ON t.usertype=c.usertype 
WHERE c.id=object_id('VotreTable') 
ORDER BY c.colid

EXEC sp_help NomTable

1
2
3
SELECT name  
  FROM sysobjects  
 WHERE type='V'

1
2
SELECT *  
  FROM information_schema.views

1
2
3
SELECT name  
  FROM sysobjects  
 WHERE type='FN'

1
2
3
SELECT name  
  FROM sysobjects  
 WHERE type='P'

1
2
SELECT *  
FROM INFORMATION_SCHEMA.ROUTINES

1
2
3
4
5
6
7
8
9
10
SELECT  
    o.name,  o.xtype,  c.text,    u.name,   o.crdate  
FROM  
    dbo.sysobjects o  
INNER JOIN dbo.syscomments c 
    ON c.id = o.id  
INNER JOIN dbo.sysusers u 
    ON u.uid = c.uid  
WHERE  
    xtype = 'TR'

EXEC sp_pkeys @table_name='MaTable'

sp_help MaTable

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
select  
 column_name as champ,  
COALESCE(domain_name,  
cast(data_type as varchar(128))+ ISNULL(' ' + cast(character_maximum_length as varchar(10)) ,'')) as type_donnee,  
CASE UPPER(IS_NULLABLE)  
   when 'YES' then ''  
   when 'NO' then 'Oui'  
   when Null then ''  
   else  IS_NULLABLE  
END as Obligatoire,  
'' as description  
from INFORMATION_SCHEMA.columns  
where  
 table_name  = 'Matable'  
order by table_name, ordinal_position

1
2
3
4
select cdefault 
from syscolumns 
where id = object_id('VotreTable') 
and name = 'VotreColonne'

1
2
3
4
Select O.Name as Table_Name, I.Rows as Rows_Count 
FROM sysobjects O join sysindexes I 
          ON O.id=I.id 
Where O.xtype='U'

SELECT  DB_NAME() AS BASE_DE_DONNEES_EN_COURS

1
2
3
SELECT CATALOG_NAME  
FROM INFORMATION_SCHEMA.SCHEMATA 
Go

1
2
3
4
5
USE master 
Go 
SELECT name as BaseDedonneeDuServeur 
FROM sysdatabases 
Go

1
2
EXEC sp_databases 
go

1
2
ALTER TABLE MyTable  
ALTER COLUMN MyColumn NVARCHAR(20) NOT NULL

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
--Supposons que nous ayant une table T_Person dont la definition est : 
CREATE TABLE Tmp_T_PERSONNE 
( 
PER_ID int NOT NULL, 
PER_NOM varchar(50) NOT NULL, 
PER_PRENOM varchar(50) NULL, 
PER_NE_LE smalldatetime NOT NULL, 
)  ON [PRIMARY] 
GO 
--Et que nous voulons changer le type Per_Nom du type varchar(50) au type varchar(100) 
--Nous aurons : 
BEGIN TRANSACTION 
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE 
--Créer une table temporaire ayant même structure que la première 
CREATE TABLE Tmp_T_PERSONNE 
( 
PER_ID int NOT NULL, 
PER_NOM varchar(100) NOT NULL, 
PER_PRENOM varchar(50) NULL, 
PER_NE_LE smalldatetime NOT NULL, 
)  ON [PRIMARY] 
GO 
-- Peupler la table 
IF EXISTS(SELECT * FROM T_PERSONNE) 
 EXEC('INSERT INTO Tmp_T_PERSONNE (PER_ID,PER_NOM, PER_PRENOM, PER_NE_LE, PAY_ID, PER_NE_A) 
SELECT PER_ID, PER_NOM, PER_PRENOM, PER_NE_LE FROM T_PERSONNE TABLOCKX') 
GO 
--Supprimer la table 
DROP TABLE dbo.T_PERSONNE 
GO 
--Renommer la nouvelle table avec l'ancien nom 
EXECUTE sp_rename N'Tmp_T_PERSONNE', N'T_PERSONNE', 'OBJECT' 
GO 
COMMIT

EXEC sp_renamedb('MyOldDB','MyNiewDB')

1
2
3
sp_rename [ @objname = ] 'object_name' ,  
    [ @newname = ] 'new_name'  
    [ , [ @objtype = ] 'object_type' ]

EXEC sp_rename 'customers', 'custs'

EXEC sp_rename 'customers.[contact title]', 'title', 'COLUMN'

1
2
3
4
5
select text  
from dbo.syscomments, dbo.sysobjects 
where syscomments.id = sysobjects.id 
And sysobjects.xtype = 'P' 
AND sysobjects.name='MaProcédure'

sp_helptext 'MaProcédure'

Database diagram support objects cannot be installed because this database does not have a valid owner. To continue, first use the Files page of the Database Properties dialog box or the ALTER AUTHORIZATION statement to set the database owner to a valid login, then add the database diagram support objects.

ALTER AUTHORIZATION ON DATABASE::database_name TO valid_login

1
2
3
SELECT *  
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS  
WHERE TABLE_NAME = 'matable'

1
2
3
4
5
6
7
SELECT DISTINCT NECESSAIRE.NAME 
FROM SYSOBJECTS AS NECESSAIRE 
INNER JOIN SYSDEPENDS AS DEPENDENCES 
ON NECESSAIRE.ID = DEPENDENCES.depid 
INNER JOIN SYSOBJECTS AS DEPENDANTE 
ON DEPENDENCES.id = DEPENDANTE.id 
WHERE DEPENDANTE.name='NOMDELAVUE'

1
2
3
4
5
6
select s1.name, s1.type, s2.name, s2.type  
from syscolumns s1, syscolumns s2 
where s1.id = object_id('MaTable1') 
and s2.id = object_id('MaTable2') 
and s1.name=s2.name 
and s1.type<>s2.type

1
2
3
4
5
6
7
8
9
10
11
DECLARE @SCH NVARCHAR(128), @TAB  NVARCHAR(128);  
  
SELECT @SCH = '???' , @TAB = '???';  
  
DECLARE @SQL NVARCHAR(max)  
SET @SQL = '';  
SELECT @SQL = @SQL + 'SELECT * FROM '  
       + name + '.INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = ''' + COALESCE(@SCH, 'dbo') + '''  AND TABLE_NAME = ''' + @TAB +''';'  
FROM   sys.databases;  
  
EXEC (@SQL);

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
USE msdb;  
GO  
  
CREATE SCHEMA S_SYS  
  
CREATE TABLE T_A_DISK_DSK  
( DSK_ID        INT      NOT NULL PRIMARY KEY,  
  DSK_UNIT      CHAR(1)  NOT NULL UNIQUE CHECK (DSK_UNIT COLLATE French_CI_AS BETWEEN 'C' AND 'Z'),  
  DSK_ALERT_PC  FLOAT    NOT NULL DEFAULT 30.0 CHECK (DSK_ALERT_PC BETWEEN 0.0 AND 100.0))  
  
CREATE TABLE T_A_TRACE_SPACE_DISK_TSP  
( TSP_ID        INT      NOT NULL PRIMARY KEY,  
  DSK_UNIT      CHAR(1)  NOT NULL FOREIGN KEY REFERENCES T_A_DISK_DSK (DSK_UNIT),  
  TSP_DATETIME  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,  
  TSP_SIZE_MO   INT      NOT NULL,  
  TSP_USED_MO   INT      NOT NULL);  
GO    
  
CREATE INDEX X_TSP_DTM ON S_SYS.T_A_TRACE_SPACE_DISK_TSP (TSP_DATETIME, DSK_UNIT);  
GO

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
CREATE PROCEDURE S_SYS.P_AUDIT_SPACE_DISK  
AS  
  
SET NOCOUNT ON;  
  
DECLARE @HDL int,  
        @FSO int,  
        @HD char(1),  
        @DRV int,  
        @SZ varchar(20),  
        @MB bigint ;   
  
SET @MB = 1048576;  
  
CREATE TABLE #HD (HD_UNIT      char(1) PRIMARY KEY,  
                  HD_FREESPACE int NULL,  
                  HD_SIZE      int NULL);  
  
INSERT INTO #HD (HD_UNIT, HD_FREESPACE)   
EXEC master.dbo.xp_fixeddrives;  
  
DELETE FROM #HD  
WHERE  HD_UNIT NOT IN (SELECT DSK_UNIT   
                       FROM   S_SYS.T_A_DISK_DSK);  
  
EXEC @HDL = sp_OACreate 'Scripting.FileSystemObject',@FSO OUT;  
IF @HDL <> 0 EXEC sp_OAGetErrorInfo @FSO;  
  
DECLARE C CURSOR LOCAL FAST_FORWARD  
FOR SELECT HD_UNIT   
    FROM   #HD;  
  
OPEN C;  
  
FETCH NEXT FROM C INTO @HD;  
  
WHILE @@FETCH_STATUS=0  
BEGIN  
  
        EXEC @HDL = sp_OAMethod @FSO, 'GetDrive', @DRV OUT, @HD  
        IF @HDL <> 0 EXEC sp_OAGetErrorInfo @FSO;  
  
        EXEC @HDL = sp_OAGetProperty @DRV, 'TotalSize', @SZ OUT  
        IF @HDL <> 0 EXEC sp_OAGetErrorInfo @DRV;  
  
        UPDATE #HD  
        SET    HD_SIZE = CAST(@SZ AS FLOAT) / @MB  
        WHERE  HD_UNIT = @HD;  
  
        FETCH NEXT FROM C INTO @HD;  
  
END  
  
CLOSE C;  
DEALLOCATE C;  
  
EXEC @HDL=sp_OADestroy @FSO;  
IF @HDL <> 0 EXEC sp_OAGetErrorInfo @FSO;  
  
INSERT INTO S_SYS.T_A_TRACE_SPACE_DISK_TSP (TSP_UNIT, TSP_SIZE_MO, TSP_USED_MO)  
SELECT                                      HD_UNIT,  HD_SIZE,     HD_SIZE - HD_FREESPACE  
FROM   #HD  
  
DROP TABLE #HD;  
  
RETURN;  
GO

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
USE [msdb]  
GO  
  
EXEC msdb.dbo.sp_add_job   
   @job_name=N'Scrutation espace disque',   
   @enabled=1,   
   @notify_level_eventlog=0,   
   @notify_level_email=2,   
   @notify_level_netsend=2,   
   @notify_level_page=2,   
   @delete_level=0,   
   @category_name=N'Data Collector',   
   @owner_login_name=N'SA';  
  
EXEC msdb.dbo.sp_add_jobserver   
   @job_name=N'Scrutation espace disque',   
   @server_name = N'ServerSQL[\instance]';  
  
EXEC msdb.dbo.sp_add_jobstep   
   @job_name=N'Scrutation espace disque',   
   @step_name=N'Rapporte l''état du volume du disque',   
   @step_id=1,   
   @cmdexec_success_code=0,   
   @on_success_action=1,   
   @on_fail_action=2,   
   @retry_attempts=0,   
   @retry_interval=0,   
   @os_run_priority=0,   
   @subsystem=N'TSQL',   
   @command=N'EXEC S_SYS.P_AUDIT_SPACE_DISK;',   
   @database_name=N'msdb',   
   @flags=0;  
  
EXEC msdb.dbo.sp_update_job   
   @job_name=N'Scrutation espace disque',   
   @enabled=1,   
   @start_step_id=1,   
   @notify_level_eventlog=0,   
   @notify_level_email=2,   
   @notify_level_netsend=2,   
   @notify_level_page=2,   
   @delete_level=0,   
   @description=N'',   
   @category_name=N'Data Collector',   
   @owner_login_name=N'ServerSQL[\instance]',   
   @notify_email_operator_name=N'',   
   @notify_netsend_operator_name=N'',   
   @notify_page_operator_name=N'';  
  
EXEC msdb.dbo.sp_add_jobschedule  
   @job_name=N'Scrutation espace disque',   
   @name=N'Planification espace disque',   
   @enabled=1,   
   @freq_type=4,   
   @freq_interval=1,   
   @freq_subday_type=1,   
   @freq_subday_interval=0,   
   @freq_relative_interval=0,   
   @freq_recurrence_factor=1,   
   @active_start_date=20090312,   
   @active_end_date=99991231,   
   @active_start_time=50000,   
   @active_end_time=235959;  
GO

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
-- trigger pour envoi d'un mail  
CREATE TRIGGER E_I_TSP  
ON S_SYS.T_A_TRACE_SPACE_DISK_TSP  
FOR INSERT  
AS  
BEGIN  
   IF EXISTS(SELECT *  
             FROM   inserted AS i  
                    INNER JOIN S_SYS.T_A_DISK_DSK AS D  
                          ON i.DSK_UNIT = D.DSK_UNIT  
             WHERE  (CAST(TSP_USED_MO AS FLOAT) / CAST(TSP_SIZE_MO AS FLOAT) ) * 100 > DSK_ALERT_PC  
      master..sp_send_dbmail @profile_name = 'profile_name',  
                             @recipients = 'mail_destinataire1;mail_destinataire2;mail_destinataire3;...',  
                             @subject = 'Alerte automatique : quota d''espace libre de disque en dessous de la valeur fixée',   
                             @body = 'Au moins un des disques que vous suivez via la procédure cyclique  
S_SYS.P_AUDIT_SPACE_DISK a atteint un quota d''espace disque inférieur au seuil d''alerte fixé.',  
                             @body_format = 'TEXT',  
                             @importance = 'High',  
                             @query = 'SELECT D.DSK_UNIT AS UNITE, TSP_SIZE_MO AS TAILLE_MO, TSP_USED_MO AS ESPACE_UTILISE_MO,   
       (CAST(TSP_USED_MO AS FLOAT) / CAST(TSP_SIZE_MO AS FLOAT) ) * 100 AS OCCUPATION_POURCENT,  
       DSK_ALERT_PC AS TAUX_ALERTE_POURCENT,  
       CASE   
          WHEN (CAST(TSP_USED_MO AS FLOAT) / CAST(TSP_SIZE_MO AS FLOAT) ) * 100 > DSK_ALERT_PC 
          THEN ''CRITIQUE !''  
          ELSE ''normal''   
       END AS ETAT     
FROM   S_SYS.T_A_TRACE_SPACE_DISK_TSP AS T  
       INNER JOIN S_SYS.T_A_DISK_DSK AS D  
             ON T.DSK_UNIT = D.DSK_UNIT  
WHERE  TSP_DATETIME = (SELECT MAX(TSP_DATETIME)  
                       FROM   S_SYS.T_A_TRACE_SPACE_DISK_TSP)  
ORDER  BY 1 ',  
                             @execute_query_database =  'msdb',  
                             @attach_query_result_as_file = 0,  
                             @query_result_header = 1;  
END  
GO

1
2
INSERT INTO S_SYS.T_A_DISK_DSK VALUES ('C', 70);  
INSERT INTO S_SYS.T_A_DISK_DSK VALUES ('D', 50);