Cette liste de procédures et de fonction permet de rechercher ou de décrire les objets de votre base de données MS SQL Server.
Elle utilise essentiellement des commandes Transact SQL.
1 – Recherche d’une table (ou d’une vue) dans toutes les bases
USE master;
GO
CREATE PROCEDURE dbo.sp__SEARCH_TABLE_ALL_SERVER
@SCHEMA NVARCHAR(128),
@TABLE NVARCHAR(128),
@JOKER BIT,
@ESCAPE NCHAR(1)
AS
/******************************************************************************
* MODULE : META *
* NATURE : PROCEDURE *
* OBJECT : master.dbo.sp__SEARCH_TABLE_ALL_SERVER *
* CREATE : 2018-05-28 *
* VERSION : 1 *
*******************************************************************************
* Frédéric BROUARD - alias SQLpro - SARL SQL SPOT - SQLpro@sqlspot.com *
* Architecte de données : expertise, audit, conseil, formation, modélisation *
* tuning, sur les SGBD Relationnels, le langage SQL, MS SQL Server/PostGreSQL *
* blog: http://mssqlserver.fr website: http://sqlpro.developpez.com *
* Most Valuable Professional - MVP - SQL Server (data platform) depuis 2004 *
*******************************************************************************
* PURPOSE : search a user<code>s table in all the databases of an instance *
*******************************************************************************
* INPUTS : *
* @SCHEMA NVARCHAR(128) the name of the table</code>s schema in which we search *
* @TABLE NVARCHAR(128) the name of the table to be search *
* @JOKER BIT if 1 use LIKE and accepts LIKE`s jokers *
* @ESCAPE NCHAR(1) nchar for escpaping from LIKE *
*******************************************************************************
* OUPUT : table as describe : *
* TABLE_CATALOG sysname the database name *
* TABLE_SCHEMA sysname the table schema name *
* TABLE_NAME sysname the table name *
* TABLE_TYPE varchar(10) the table type (BASE TABLE or VIEW) *
*******************************************************************************
* RUNNABLE : version 2008 and superior *
*******************************************************************************
* SAMPLES : *
* EXEC sp__SEARCH_TABLE_ALL_SERVER NULL, 'LOT', NULL, NULL *
* EXEC sp__SEARCH_TABLE_ALL_SERVER 'dbo', 'LOT', NULL, NULL *
* EXEC sp__SEARCH_TABLE_ALL_SERVER NULL, 'LOT%', 1, NULL *
* EXEC sp__SEARCH_TABLE_ALL_SERVER NULL, 'T?_%', 1, '?' *
******************************************************************************/
DECLARE @SQL NVARCHAR(max);
SET @SQL = N'';
SET @SCHEMA = COALESCE('''' + @SCHEMA + '''', 'NULL');
SELECT @SQL = @SQL
+ N'SELECT TABLE_CATALOG COLLATE SQL_Latin1_General_CP850_BIN AS [DATABASE_NAME], '
+ N'TABLE_SCHEMA COLLATE SQL_Latin1_General_CP850_BIN AS TABLE_SCHEMA, '
+ N'TABLE_NAME COLLATE SQL_Latin1_General_CP850_BIN AS TABLE_NAME, '
+ N'TABLE_TYPE COLLATE SQL_Latin1_General_CP850_BIN AS TABLE_NAME FROM ['
+ name + N'].INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA '
+ CASE @JOKER WHEN 1
THEN N'LIKE '
ELSE N'= '
END + N' COALESCE(' + @SCHEMA + N', TABLE_SCHEMA) '
+ CASE WHEN @JOKER = 1 AND @ESCAPE IS NOT NULL
THEN 'ESCAPE ''' + @ESCAPE + ''''
ELSE N'' END + N' AND TABLE_NAME '
+ CASE @JOKER WHEN 1
THEN N'LIKE '
ELSE N'= '
END + '''' + @TABLE + N''' '
+ CASE WHEN @JOKER = 1 AND @ESCAPE IS NOT NULL
THEN 'ESCAPE ''' + @ESCAPE + ''''
ELSE N'' END + N' UNION ALL '
FROM sys.databases;
SET @SQL = LEFT(@SQL, LEN(@SQL) - 10);
PRINT @SQL
EXEC (@SQL);
GO
EXEC sp_MS_marksystemobject 'sp__SEARCH_TABLE_ALL_SERVER';
Cette procédure « système »(1) est utilisable depuis n’importe quelle base et recherche dans toutes les bases, y compris les bases systèmes, une table dont le nom ou le nom et le schéma sont passé en argument. Si le nom du schéma n’est pas donnée, alors la procédure recherche à travers tous les schémas de toutes les bases. Vous pouvez utiliser les joker du like en passant la valeur de l’argument JOKER à 1 et même prévoir un caractère d’échappement en le précisant dans la valeur de l’argument ESCAPE.
Exemples d’utilisation :
EXEC sp__SEARCH_TABLE_ALL_SERVER NULL, 'LOT', NULL, NULL; EXEC sp__SEARCH_TABLE_ALL_SERVER 'dbo', 'LOT', NULL, NULL; EXEC sp__SEARCH_TABLE_ALL_SERVER NULL, 'LOT%', 1, NULL; EXEC sp__SEARCH_TABLE_ALL_SERVER NULL, 'T?_%', 1, '?';
Le lien de téléchargement du code
2 – Suppression de tout commentaires et caractères non imprimable dans du code d’une routine
CREATE FUNCTION dbo.F_META_PURGE_CODE
(@SQL NVARCHAR(max))
RETURNS NVARCHAR(max)
WITH RETURNS NULL ON NULL INPUT
AS
/******************************************************************************
* MODULE : META *
* NATURE : SCALAR_FUNCTION *
* OBJECT : dbo.F_META_PURGE_CODE *
* CREATE : 2018-05-28 *
* VERSION : 1 *
*******************************************************************************
* Frédéric BROUARD - alias SQLpro - SARL SQL SPOT - SQLpro@sqlspot.com *
* Architecte de données : expertise, audit, conseil, formation, modélisation *
* tuning, sur les SGBD Relationnels, le langage SQL, MS SQL Server/PostGreSQL *
* blog: http://mssqlserver.fr website: http://sqlpro.developpez.com *
* Most Valuable Professional - MVP - SQL Server (data platform) depuis 2004 *
*******************************************************************************
* PURPOSE : purges Transact SQL code of comments and unprintable characters *
*******************************************************************************
* INPUTS : *
* @SQL NVARCHAR(max) : the Transact SQL code to be purged *
*******************************************************************************
* OUPUT : VARCHAR(max) *
*******************************************************************************
* RUNNABLE : version 2000 and superior *
*******************************************************************************
* SAMPLE : *
* SELECT object_id, definition, *
* dbo.F_META_PURGE_CODE(definition) *
* FROM sys.sql_modules; *
******************************************************************************/
BEGIN
IF @SQL = N'' -- on empty string return empty string
RETURN N'';
DECLARE @NC1 NCHAR(1), -- char to be treated
@NC2 NCHAR(2), -- 2 chars to be treated
@I INT, -- pos of char to be treated
@OUT NVARCHAR(max), -- returned string
@TT BIT, -- 1 if comment as «--»
@SE INT, -- n if comment as «/* */» (can be nested !)
@CC BIT; -- 1 if in as string
-- first assignements
SELECT @OUT = N'', -- output is empty string to begin
@I = 1, -- position of first char
@TT = 0, -- on the beginning, no comment «--»
@SE = 0, -- on the beginning, no comment «/*»
@CC = 0; -- on the beginning, no string"
-- loop while there is a char to treat
WHILE @I <= LEN(@SQL)
BEGIN
-- getting the chars
SELECT @NC2 = SUBSTRING(@SQL, @I, 2), -- get the 2 chars at position I
@NC1 = SUBSTRING(@SQL, @I, 1); -- get the char at position I
-- this is the beginning or the ending of a string (not in comment)
-- so we reverse the position of the bit @CC
IF @NC1 = '''' AND @TT = 0 AND @SE = 0
IF @CC = 1
SET @CC = 0
ELSE
SET @CC = 1;
-- we are in a string, add the char in the output and loop again
IF @CC = 1
BEGIN
SET @OUT = @OUT + @NC1
SET @I = @I + 1;
CONTINUE;
END
-- we are not in a string neither in a comment, and the next 2 chars are
-- not a comment's beginning. Add the char in the output and loop again
IF @TT = 0 AND @SE = 0 AND @CC = 0 AND @NC2 NOT IN (N'--', '/*')
BEGIN
SET @OUT = @OUT + @NC1
SET @I = @I + 1;
CONTINUE;
END
-- we are not in "--" comment and the next 2 chars are a "/*" comment
-- increments "/*" counter and pass 2 chars
IF @TT = 0 AND @NC2 = NCHAR(47) + NCHAR(42)
BEGIN
SELECT @SE = @SE + 1,
@I = @I + 2;
CONTINUE;
END;
-- we are not in "/*" comment and the next 2 chars are a "--" comment
-- set "--" bit to 1 and pass 2 chars
IF @TT = 0 AND @SE = 0 AND @NC2 = N'--'
BEGIN
SELECT @TT = 1,
@I = @I + 2;
CONTINUE;
END;
-- we are in "/*" comment and the next 2 chars are a "*/" comment end
-- decrease the "/*" counter and pass 2 chars
IF @TT = 0 AND @SE > 0 AND @NC2 = NCHAR(42) + CHAR(47)
BEGIN
SET @SE = @SE - 1;
SET @I = @I + 2;
CONTINUE;
END;
-- we are in "--" comment and the next 1 or 2 chars are a "--" comment end
-- set "--" bit to 1 and pass 1 or 2 chars
IF @TT = 1 AND @SE = 0 AND @NC1 IN (NCHAR(13), NCHAR(10))
BEGIN
SET @TT = 0
IF @NC2 = NCHAR(13) + NCHAR(10)
SET @I = @I + 2
ELSE
SET @I = @I + 1
CONTINUE;
END;
-- in all other conditions pass one char
SET @I = @I + 1;
END;
-- clear the string for remaining ends of line or tab
SET @OUT = REPLACE(REPLACE(REPLACE(@OUT, NCHAR(9), N' '),
NCHAR(10), N' '),
NCHAR(13), N' ');
-- clear the string of duplicate spaces except in a string (' ') or in a bracketed SQL identifier
DECLARE @KK BIT = 0, -- identifies if we are in a SQL identifier with brackets [...]
@EE BIT = 0; -- identifies if we are in a string ' ... '
-- initializing
SELECT @SQL = @OUT,
@I = 0,
@CC = 0,
@OUT = N'';
-- loop over the chars
WHILE @I <= LEN(@SQL)
BEGIN
SET @I = @I + 1;
SET @NC1 = SUBSTRING(@SQL, @I, 1);
-- beginning of an identifier with brackets
IF @NC1 = '[' AND @KK = 0 AND @CC = 0
BEGIN
SELECT @KK = 1, @EE = 0;
SET @OUT = @OUT + @NC1
CONTINUE;
END;
-- ending of an identifier with brackets
IF @NC1 = ']' AND @KK = 1
BEGIN
SELECT @KK = 0, @EE = 0;
SET @OUT = @OUT + @NC1
CONTINUE;
END;
-- beginning of a string with simple quote
IF @NC1 = '''' AND @KK = 0 AND @CC = 0
BEGIN
SELECT @CC = 1, @EE = 0;
SET @OUT = @OUT + @NC1
CONTINUE;
END;
-- ending of a string with simple quote
IF @NC1 = '''' AND @CC = 1
BEGIN
SELECT @CC = 0, @EE = 0;
SET @OUT = @OUT + @NC1
CONTINUE;
END;
-- firts space we keep it always
IF @NC1 = ' ' AND @EE = 0
BEGIN
SET @OUT = @OUT + @NC1
SET @EE = 1;
CONTINUE
END
-- space following a space, in bracketed identifier or string
IF @NC1 = ' ' AND @EE = 1 AND (@KK = 1 OR @CC = 1)
BEGIN
SET @OUT = @OUT + @NC1
CONTINUE
END
-- space following a space, outside bracketed identifier or string
IF @NC1 = ' ' AND @EE = 1 AND @KK = 0 AND @CC = 0
BEGIN
CONTINUE
END
-- no more spaces
IF @NC1 <> ' ' AND @EE = 1
SET @EE = 0;
SET @OUT = @OUT + @NC1;
END;
-- trim both the string
RETURN RTRIM(LTRIM(@OUT));
END
GO
Cette fonction purge le code de tous les commentaires présent dans les routines, commentaires commençant par — ou encadrés par /* … */ ainsi que les caractères non imprimables que sont les retours chariot, fin de ligne et tabulation. Enfin, il expurge tous les espaces en double.
Exemple :
SELECT object_id, definition,
dbo.F_META_PURGE_CODE(definition)
FROM sys.sql_modules;
Le lien de téléchargement du code
3 – Procédure de description des index sp__helpindex
Tous les aficionados de SQL Server connaissent la procédures sp_helpindex qui permet de trouver les index d’une table, mais cette dernière n’a jamais été remise au goût du jour, malgré que les index aient notablement évolués : clause INCLUDE, clauses WHERE index fulltext ou géographiques, stockage sur une partition, index columnstore.
La procédure sp__helpindex données ci dessous répare ces manques (notez le double blanc souligné !
CREATE PROCEDURE dbo.sp__HELPINDEX @OBJ NVARCHAR(133)
AS
/******************************************************************************
* METADONNÉES DES INDEX *
* Procédure listant sous forme de tables les métadonnées logique des index *
*******************************************************************************
* Frédéric BROUARD - alias SQLpro - SARL SQL SPOT - SQLpro@sqlspot.com *
* Architecte de données : expertise, audit, conseil, formation, modélisation *
* tuning, sur les SGBD Relationnels, le langage SQL, MS SQL Server/PostGreSQL *
* blog: http://mssqlserver.fr website: http://sqlpro.developpez.com *
* Most Valuable Professional - MVP - SQL Server (data platform) depuis 2004 *
*******************************************************************************
* Cette procédure prend en argument le nom d'une table avec son schéma SQL *
* (sinon le schéma par défaut de l'utilisateur qui la lance) et renvoie un *
* jeu de données contenant le descriptif logique de constitution d'un index *
* ou de tous les index de la base *
* NOTA substitut à la procédure stockée système sp_helpindex *
*******************************************************************************
* ATTENTION : procédure système ! Exécutable depuis n'importe quelle base *
* *
* Paramètre en entrée : *
* @OBJ type NVARCHAR(133) : nom de table dont on veut connaître les index *
* si vide, la procédure renvoie la liste de tous les index *
* *
* COLONNE de la table en sortie : *
* TABLE_NAME : nom de la table en deux parties (schéma SQL + nom) *
* INDEX_NAME : nom de l'index (1) *
* INDEX TYPE : type d'index *
* INDEX_STORAGE : type et nom de l'espace de stockage *
* INDEX_KEY : liste des colonnes composant la clef d'index (2) *
* INDEX_INCLUDE : liste des colonnes incluses (3) *
* INDEX_FILTER : filtre d'index s'il y a lieu *
* INDEX_WITH : principaux paramètres de la clause WITH *
* *
* NOTA : *
* (1) certains index n'ont pas de nom. C'est le cas des index "fulltext" *
* (2) certains index n'ont pas de clef. C'est le cas des index *
* "columnstore" et "fulltext". Dans ce cas les colonnes indexées *
* figurent dans la clause INDEX_INCLUDE *
* (3) pour les index CLUSTERED de type BTree, toutes les colonnes autre *
* que celles de la clef étant présente, la valeur présentée est : *
* * - (INDEX_KEY) *
* pour les index CLUSTERED COLUMNSTORE toutes les colonnes de la table *
* étant présente, la valeur présentée est : * *
* *
* EXEMPLE : *
* EXEC dbo.sp__HELPINDEX NULL *
* ... donne la liste de tous les index de la base ... *
* EXEC dbo.sp__HELPINDEX '[client]' *
* ... donne la liste des index de la table client ... *
* EXEC dbo.sp__HELPINDEX 'ventes.client' *
* ... donne la liste des index de la table client du schéma SQL vente... *
* *
******************************************************************************/
SET NOCOUNT ON;
WITH
T0 AS
(
SELECT i."type", s.name + '.' + o.name AS TABLE_NAME,
i.name AS INDEX_NAME,
CASE WHEN i.is_unique = 1 THEN 'UNIQUE ' ELSE '' END +
CASE WHEN xi.xml_index_type = 0 THEN 'PRIMARY ' ELSE '' END +
i.type_desc + CASE WHEN xi.xml_index_type > 0
THEN ' FOR ' + xi.secondary_type_desc ELSE ''
END AS INDEX_TYPE,
COALESCE('FILEGROUP: ' + fg.name,
'PARTITION: ' + ps.name COLLATE database_default +'('
+ STUFF((SELECT N', ' + cp.name
FROM sys.index_columns AS icp
LEFT OUTER JOIN sys.columns AS cp
ON icp.object_id = cp.object_id
AND icp.column_id = cp.column_id
WHERE icp.object_id = i.object_id
AND icp.index_id = i.index_id
AND icp.partition_ordinal >= 1
ORDER BY icp.partition_ordinal
FOR XML PATH(N'')), 1, 1,'') +')' )
AS INDEX_STORAGE,
LTRIM(STUFF((SELECT N', ' + CONCAT(c.name, ' '
+ CASE
WHEN i.type_desc
IN ('XML',
'SPATIAL',
'COLUMNSTORE')
THEN ''
WHEN is_descending_key = 1
THEN 'DESC'
WHEN is_descending_key = 0
THEN 'ASC'
ELSE ''
END)
FROM sys.index_columns AS ic
JOIN sys.columns AS c
ON ic.object_id = c.object_id
AND ic.column_id = c.column_id
WHERE ic.object_id = i.object_id
AND ic.index_id = i.index_id
AND is_included_column = 0
ORDER BY key_ordinal
FOR XML PATH(N'')), 1, 1,'')) AS INDEX_KEY,
LTRIM(STUFF((SELECT N', ' + c.name
FROM sys.index_columns AS ic
JOIN sys.columns AS c
ON ic.object_id = c.object_id
AND ic.column_id = c.column_id
WHERE ic.object_id = i.object_id
AND ic.index_id = i.index_id
AND is_included_column = 1
ORDER BY c.name
FOR XML PATH(N'')), 1, 1,'')) AS INDEX_INCLUDE,
i.filter_definition AS INDEX_FILTER,
'FILL_FACTOR = ' + CAST(i.fill_factor AS VARCHAR(32)) +
', PAD_INDEX = ' + CAST(i.is_padded AS CHAR(1)) +
', ALLOW_ROWLOCK = ' + CAST(i.allow_row_locks AS CHAR(1)) +
', ALLOW_PAGELOCK = ' + CAST(i.allow_page_locks AS CHAR(1))
AS INDEX_WITH
FROM sys.indexes AS i
JOIN sys.objects AS o
ON i.object_id = o.object_id
JOIN sys.schemas AS s
ON o.schema_id = s.schema_id
JOIN sys.data_spaces AS ds
ON i.data_space_id = ds.data_space_id
LEFT OUTER JOIN sys.filegroups AS fg
ON ds.data_space_id = fg.data_space_id AND ds."type" = 'FG'
LEFT OUTER JOIN sys.partition_schemes AS ps
ON ds.data_space_id = ps.data_space_id AND ds."type" = 'PS'
LEFT OUTER JOIN sys.partition_functions AS pf
ON ps.function_id = pf.function_id
LEFT OUTER JOIN sys.xml_indexes AS xi
ON i.object_id = xi.object_id
AND i.index_id = xi.index_id
WHERE o.object_id = COALESCE(NULLIF(OBJECT_ID(@OBJ), 0), o.object_id)
AND i.index_id > 0
AND o.is_ms_shipped = 0
)
SELECT TABLE_NAME, INDEX_NAME, INDEX_TYPE, INDEX_STORAGE, INDEX_KEY,
CASE "type" WHEN 1 THEN '* - (INDEX_KEY)'
WHEN 5 THEN '*' END AS INDEX_INCLUDE,
INDEX_FILTER, INDEX_WITH
FROM T0
UNION ALL
SELECT s.name + '.' + o.name AS TABLE_NAME,
NULL AS INDEX_NAME, 'FULL TEXT' AS INDEX_TYPE,
'FULLTEXT CATALOG: ' + ftc.name AS INDEX_STORAGE,
NULL AS INDEX_KEY,
LTRIM(STUFF((SELECT N', ' + c.name
FROM sys.fulltext_index_columns AS ftic
JOIN sys.columns AS c
ON c.object_id = ftic.object_id
AND c.column_id = ftic.column_id
WHERE ftic.object_id = fti.object_id
FOR XML PATH(N'')), 1, 1,'')) AS INDEX_INCLUDE,
NULL AS INDEX_FILTER,
'CHANGE_TRACKING = ' + change_tracking_state_desc +
', STOPLIST = ' + CASE
WHEN fti.stoplist_id = 0 THEN 'SYSTEM'
WHEN fti.stoplist_id IS NULL THEN 'OFF'
ELSE (SELECT name
FROM sys.fulltext_stoplists AS ftsl
WHERE fti.stoplist_id = ftsl.stoplist_id)
END +
COALESCE(', SEARCH PROPERTY LIST = ' +
(SELECT name
FROM sys.registered_search_property_lists AS ftpl
WHERE fti.property_list_id = ftpl.property_list_id)
COLLATE French_BIN, '') AS INDEX_WITH
FROM sys.fulltext_indexes AS fti
JOIN sys.objects AS o
ON fti.object_id = o.object_id
JOIN sys.schemas AS s
ON o.schema_id = s.schema_id
JOIN sys.fulltext_catalogs AS ftc
ON fti.fulltext_catalog_id = ftc.fulltext_catalog_id
WHERE o.object_id = COALESCE(NULLIF(OBJECT_ID(@OBJ), 0), o.object_id)
ORDER BY TABLE_NAME, INDEX_KEY, INDEX_INCLUDE;
GO
EXEC sp_MS_marksystemobject 'sp__HELPINDEX';
Le code de la procédure à télécharger
(1) une procédure système est créée dans le schéma dbo de la base master et son nom doit commencer par ‘sp_’. Une foi marqué comme procédure système via la procédure système « sp_MS_marksystemobject », elle est appelable dans n’importe quelle base et se comporte comme une procédure locale.
Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
* Le site sur les SGBD relationnels et le SQL : https://sqlpro.developpez.com *
* le blog SQL, SQL Server, SGBDR... sur : https://blog.developpez.com/sqlpro/ *
* Expert Microsoft SQL Server, MVP (Most valuable Professional) depuis 14 ans *
* Entreprise SQL SPOT : modélisation, conseil, audit, optimisation, formation *
* * * * * Enseignant CNAM PACA - ISEN Toulon - CESI Aix en Provence * * * * *