Why the table sys.sql_modules is empty?

Question

In SSMS, I create a database using the following script. When the script execution completes, I would expect to see the CREATE TABLE statement in the sql_modules table. However I can't find it.

CREATE DATABASE [MyDb]
GO 
USE [MyDb]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[MyTable] (
    [UID] [uniqueidentifier] NOT NULL,
    [DateTime] [datetime] NOT NULL
)

Here's the query I run the get the table definition. However, I get three empty result sets. Any idea why I am getting those three empty results set?

USE MyDb
GO

SELECT * 
FROM sys.sql_modules

SELECT * 
FROM sys.triggers

SELECT sm.object_id, OBJECT_NAME(sm.object_id) AS object_name, o.type, o.type_desc, sm.definition
FROM sys.sql_modules AS sm
JOIN sys.objects AS o ON sm.object_id = o.object_id
ORDER BY o.type;

Are you creating any procedures, functions, views? tables don't show up there. [The first paragraph here says what to expect to find](http://msdn.microsoft.com/en-us/library/ms175081.aspx) — Martin Smith, Nov 01 '12 at 16:53
You need to figure them out yourself from the other views. [Example here](http://stackoverflow.com/a/317864/73226) or you can use SMO to script them. — Martin Smith, Nov 01 '12 at 16:55

score 2 · Accepted Answer · answered Nov 01 '12 at 17:05

To get columns and table information, this is a good place to start:

SELECT c.*, t.*
FROM sys.tables AS t
INNER JOIN sys.columns AS c ON t.object_id = c.object_id
INNER JOIN sys.schemas AS sch ON t.schema_id = sch.schema_id
WHERE sch.name = 'dbo'
   AND t.name = 'YourTable'

Why the table sys.sql_modules is empty?

1 Answers1