Mengetahui Definisi dalam suatu Database Object
USE AdventureWorks2008;
GO
-- Mengetahui isi Stored Procedure, Trigger, VIEW & Function
SELECT SchemaName=schema_name(schema_id),
ObjectName=object_Name(m.object_ID),
ObjectDefinition=definition
FROM sys.SQL_Modules m
INNER JOIN sys.objects o
ON m.object_id=o.object_id
USE AdventureWorks2008;
GO
-- Menampilkan code definisi suatu user procedure
SELECT SchemaName=schema_name(schema_id),
ObjectName=object_Name(m.object_ID),
ObjectDefinition=definition
FROM sys.SQL_Modules m
INNER JOIN sys.objects o
ON m.object_id=o.object_id
WHERE definition LIKE '%CREATE PROCEDURE%'
USE AdventureWorks2008;
GO
SELECT SchemaName=schema_name(schema_id),
ObjectName=object_Name(m.object_ID),
ObjectDefinition=definition
FROM sys.SQL_Modules m
INNER JOIN sys.objects o
ON m.object_id=o.object_id
WHERE definition LIKE '%CREATE FUNCTION%'
USE AdventureWorks2008;
GO
-- T-SQL untuk mengetahui isi dan definisi VIEW pada database
-- Menampilkan semua view pada database dan definisinya
SELECT SchemaName=schema_name(schema_id),
ObjectName=object_Name(m.object_ID),
ObjectDefinition=definition
FROM sys.SQL_Modules m
INNER JOIN sys.objects o
ON m.object_id=o.object_id
WHERE definition LIKE '%CREATE VIEW%'
USE AdventureWorks2008;
GO
SELECT SchemaName=schema_name(schema_id),
ObjectName=object_Name(m.object_ID),
ObjectDefinition=definition
FROM sys.SQL_Modules m
INNER JOIN sys.objects o
ON m.object_id=o.object_id
WHERE definition LIKE '%CREATE TRIGGER%'
