Sometime we have confusion that the object presents in the database or not. To find the objects present we have to query the sys.sysobjects.
I am trying to make it easy by a stored procedure.
The limitation of the stored procedure is it helps to find such common objects like Scalar function, Inlined table-func, Stored procedure, Table function, Trigger, User define Table and Views only.
It also displays the definition or text of the supplied objects if it is not encrypted.
Please note that this is a prototype only. You can enrich the functionality for betterment.
IF EXISTS(SELECT * FROM sysobjects WHERE id=OBJECT_ID('find_Obj'))
BEGIN
DROP PROCEDURE find_Obj
END
GO
CREATE PROCEDURE find_Obj
(
@p_ObjName VARCHAR(MAX)
)
AS
DECLARE @v_TYPE Varchar(MAX)
BEGIN
IF EXISTS(SELECT * FROM sys.sysobjects WHERE id=OBJECT_ID(@p_ObjName))
BEGIN
SELECT OBJECT_NAME(id), case type when 'FN' then 'Scalar function'
when 'IF' then 'Inlined table-function'
when 'P' then 'Stored procedure'
when 'TF' then 'Table function'
when 'TR' then 'Trigger'
when 'U' then 'User define Table'
when 'V' then 'View' end [Type]
FROM sys.sysobjects
WHERE id=OBJECT_ID(@p_ObjName)
SELECT @v_TYPE=type
FROM sys.sysobjects
WHERE id=OBJECT_ID(@p_ObjName)
-- Worked if not Encrypted
IF @v_TYPE='U'
BEGIN
EXEC('sp_columns '+ @p_ObjName)
END
ELSE IF @v_TYPE='FN'
OR @v_TYPE='FN'
OR @v_TYPE='IF'
OR @v_TYPE='TR'
OR @v_TYPE='V'
BEGIN
EXEC('sp_helptext '+ @p_ObjName)
END
ELSE
BEGIN
SELECT ' Defination Unknown'
END
END
END
GO
Hope you like it.
Posted by: MR. JOYDEEP DAS.
1. sys.sysobjects has been deprecated. Use sys.objects instead.
ReplyDelete2. Your case statement is unnecessary. Select type_desc for a description of the object type.
3. A call to sp_columns is unnecessary. Look into joining sys.schemas with sys.objects and sys.columns (see below)
4. Your procedure completely ignores schemas. This is OK if you put all objects into a single schema, but as soon as you stray from the [dbo] schema, it's going to return inconsistent result (for example, if you have two tables or view with the same name in different schemas).
Examples:
-- for object structure
SELECT
[o].[type_desc] as [object_type_description],
[s].[name] as [schema_name],
[o].[name] as [object_name],
[c].[name] as [column_name],
[t].[name] as [column_type]
FROM
[sys].[schemas] [s]
INNER JOIN [sys].[objects] [o] ON [s].[schema_id] = [o].[schema_id]
LEFT JOIN [sys].[columns] [c] ON [o].[object_id] = [c].[object_id] -- left join is necessary because some objects don't have columns
LEFT JOIN [sys].[types] [t] ON [c].[user_type_id] = [t].[user_type_id]
-- for object definition
SELECT
[o].[type_desc] as [object_type_description],
[s].[name] as [schema_name],
[o].[name] as [object_name],
[sm].[definition] as [object_definition]
FROM
[sys].[schemas] [s]
INNER JOIN [sys].[objects] [o] ON [s].[schema_id] = [o].[schema_id]
INNER JOIN [sys].[sql_modules] [sm] ON [o].[object_id] = [sm].[object_id]
First of All thanks "marc" for your valuable comments.
DeleteI already mentioned that "Please note that this is a prototype only. You can enrich the functionality for betterment."
Anyway you commented a good thing that help my readers.