SELECT OBJECT_DEFINITION(object_id) FROM sys.objects returns NULL for Tables only

Question

Using the following sp I plan to export my object definitions to text files, and then import them into SVN. The sp works fine for stored procedures and views. However, when used for user tables it returns me a correctly named file for each table, but the contents are the string 'NULL' only.

The user I am trying to execute this as is an sql user. I have run the following:

USE [master]
GO
GRANT VIEW ANY Definition TO bob

This does not seem to make any difference. I have also tried the alternate syntax for running in a specific database.

USE [xxxx]
GO
GRANT VIEW Definition TO bob

What permission am I missing here? Or is it a code problem? Database server is MS SQL 2008R2 SP2

USE [xxxx]
GO
/****** Object:  StoredProcedure [dbo].[sp_Export_Objects_to_Files]    Script Date: 02/10/2014 10:52:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
----------------------------------------------------------
-- Author: xxxx
-- What: Exports objects to individual text files 
-- Why: Useful for importing to version control
-- Notes: The user running the SQL server instance will 
--      require access to the output path
-----------------------------------------------------------
/*************************************
TYPE    type_desc
AF  AGGREGATE_FUNCTION
C   CHECK_CONSTRAINT
D   DEFAULT_CONSTRAINT
F   FOREIGN_KEY_CONSTRAINT
FN  SQL_SCALAR_FUNCTION
FS  CLR_SCALAR_FUNCTION
IF  SQL_INLINE_TABLE_VALUED_FUNCTION
IT  INTERNAL_TABLE
P   SQL_STORED_PROCEDURE
PC  CLR_STORED_PROCEDURE
PK  PRIMARY_KEY_CONSTRAINT
S   SYSTEM_TABLE
SQ  SERVICE_QUEUE
TF  SQL_TABLE_VALUED_FUNCTION
TR  SQL_TRIGGER
U   USER_TABLE
UQ  UNIQUE_CONSTRAINT
V   VIEW
X   EXTENDED_STORED_PROCEDURE
****************************************/

ALTER PROCEDURE [dbo].[sp_Export_Objects_to_Files]
    @obj_type   varchar(2),
    @server     varchar(100),
    @user       varchar(100),
    @pass       varchar(100),
    @db         varchar(100),
    @path       varchar(100)
AS
BEGIN

    IF RIGHT(@path, 1) <> '\'
    BEGIN
        SET @path = @path + '\'
    END

    declare @obj_name varchar(1000)
    DECLARE @cmd varchar(4000)
    DECLARE @sql varchar(1000)

    IF OBJECT_ID('tempdb..#sptemp') IS NOT NULL DROP TABLE #sptemp

    CREATE TABLE #sptemp
    (
        obj_name varchar(1000)
    )


    INSERT INTO #sptemp (obj_name)
    SELECT OBJECT_NAME(object_id)
    FROM sys.objects
    WHERE type = @obj_type

    --select * from #sptemp

    DECLARE my_cursor CURSOR FOR  
    SELECT  obj_name
    FROM    #sptemp
    ORDER BY obj_name

    OPEN my_cursor  
    FETCH NEXT FROM my_cursor INTO @obj_name

    WHILE @@FETCH_STATUS = 0  
    BEGIN
        SET @sql = 'SET NOCOUNT ON;SELECT OBJECT_DEFINITION(object_id) FROM sys.objects WHERE type='''+ @obj_type + ''' AND name='''  + @obj_name + ''''
        SET @cmd = 'sqlcmd -S tcp:' + @server + ' -U ' + @user + ' -P ' + @pass + ' -d ' + @db + ' -y 0 -Q "' + @sql + '" -o ' + @path + @obj_name + '.sql'
        PRINT @cmd
        EXEC master..xp_cmdshell @cmd

        FETCH NEXT FROM my_cursor INTO @obj_name
    END

    close my_cursor
    deallocate my_cursor
END -- end of sp

The SQL Server Database Engine assumes that object_id is in the current database context. The collation of the object definition always matches that of the calling database context. — mr R, Mar 18 '19 at 09:54

score -1 · Answer 1 · answered Apr 08 '14 at 01:05

-1

I've been told that OBJECT_DEFINITION() doesn't work for tables

answered Apr 08 '14 at 01:05

Hecatonchires

1,009
4
13
42

SELECT OBJECT_DEFINITION(object_id) FROM sys.objects returns NULL for Tables only

1 Answers1