Search Objects For Particular DataType

Posted: November 6, 2008 in SQL Server 2005

To search through all objects for a particular data type, open a new query window and execute the following line of code:

-- Select all objects that have a particular datatype
SELECT [sys].[All_Objects].[Name] AS [TableName]
      ,[sys].[All_Columns].[Name] AS [ColumnName]
      ,[sys].[Types].[Name]       AS [DataType]
FROM   [sys].[All_Columns]
       INNER JOIN [sys].[All_Objects] ON [sys].[All_Columns].[Object_Id] =
                                         [sys].[All_Objects].[Object_Id]
       INNER JOIN [sys].[Types]       ON [sys].[All_Columns].[System_Type_Id] =
                                         [sys].[Types].[System_Type_Id]
WHERE  [sys].[Types].[Name]       = ''
AND    [sys].[All_Objects].[Type] = '';

To find all data types for a database, open a new query window and execute the following line of code:

-- Get all data types
SELECT   [Name]
FROM     [sys].[Types]
ORDER BY [Name];

To find all object types for a database, open a new query window and execute the following line of code:

-- Get all object types
SELECT   DISTINCT [Type]
                 ,[Type_Desc]
FROM     [sys].[All_Objects]
ORDER BY [Type];
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s