[SQL Server] Afficher la liste des objets et les informations concernant les tables, les procédures stockées et les fonctions
SELECT OBJECT_NAME(id)
FROM syscomments
WHERE [text] LIKE '%MEKTABA%'
AND OBJECTPROPERTY(id, 'IsProcedure') = 1
GROUP BY OBJECT_NAME(id)
Résultat :
Nom_Objet
sp_addmergearticle
sp_cleanup_log_shipping_history
sp_columns_ex
sp_columns_ex_100
sp_columns_ex_90
SELECT sys.tables.name AS [TABLE], sys.tables.create_date AS CREATE_DATE,
sys.tables.modify_date AS MODIFY_DATE,
CASE WHEN sys.database_principals.name IS NULL THEN SCHEMA_NAME(sys.tables.schema_id)
ELSE sys.database_principals.name END AS OWNER,
SUM(ISNULL(CASE INDEXES.TYPE WHEN 0 THEN COUNT_TYPE END, 0)) AS COUNT_HEAP_INDEX,
SUM(ISNULL(CASE INDEXES.TYPE WHEN 1 THEN COUNT_TYPE END, 0)) AS COUNT_CLUSTERED_INDEX,
SUM(ISNULL(CASE INDEXES.TYPE WHEN 2 THEN COUNT_TYPE END, 0)) AS COUNT_NONCLUSTERED_INDEX,
SUM(ISNULL(CASE INDEXES.TYPE WHEN 3 THEN COUNT_TYPE END, 0)) AS COUNT_XML_INDEX,
SUM(ISNULL(CASE INDEXES.TYPE WHEN 4 THEN COUNT_TYPE END, 0)) AS COUNT_SPATIAL_INDEX,
sys.tables.max_column_id_used AS COUNT_COLUMNS, sys.partitions.rows AS COUNT_ROWS,
SUM(ISNULL(CASE WHEN sys.allocation_units.type <> 1 THEN USED_PAGES
WHEN SYS.partitions.INDEX_ID < 2 THEN DATA_PAGES ELSE 0 END, 0)) *
(SELECT low / 1024 AS VALUE FROM master.dbo.spt_values
WHERE (number = 1) AND(type = N'E')) AS SIZE_DATA_KB,
SUM(ISNULL(sys.allocation_units.used_pages - CASE WHEN sys.allocation_units.type <> 1 THEN USED_PAGES
WHEN SYS.partitions.INDEX_ID < 2 THEN DATA_PAGES ELSE 0 END, 0)) *(SELECT low / 1024 AS VALUE
FROM master.dbo.spt_values AS spt_values_2 WHERE (number = 1) AND(type = N'E')) AS SIZE_INDEX_KB
FROM sys.allocation_units INNER JOIN sys.partitions ON sys.allocation_units.container_id = sys.partitions.partition_id
INNER JOIN(SELECT TOP (100) PERCENT object_id, index_id, type AS TYPE, COUNT(*) AS COUNT_TYPE
FROM sys.indexes AS indexes_1 GROUP BY object_id, type, index_id ORDER BY object_id) AS INDEXES ON
sys.partitions.object_id = INDEXES.object_id AND sys.partitions.index_id = INDEXES.index_id RIGHT OUTER JOIN
sys.database_principals RIGHT OUTER JOIN sys.tables ON sys.database_principals.principal_id = sys.tables.principal_id ON
INDEXES.object_id = sys.tables.object_id GROUP BY sys.tables.name, sys.tables.create_date, sys.tables.modify_date,
CASE WHEN sys.database_principals.name IS NULL THEN SCHEMA_NAME(sys.tables.schema_id) ELSE sys.database_principals.name END,
sys.tables.max_column_id_used, sys.partitions.rows
ORDER BY COUNT_ROWS DESC
--------- On peux aussi utiliser les commandes ci-dessous ---------
--1--Afficher la decription de la procédure stockée ou de la fonction
sp_help 'ProcedureStockee';
--2--Afficher le code source de la procédure stockée ou de la fonction
sp_helptext 'ProcedureStockee';
--3--Afficher la structure de la table
sp_help 'Table';