Solved

Function does not return the correct result

Posted on 2013-11-22
13
461 Views
Last Modified: 2013-11-22
I have the following function in my master database:

CREATE FUNCTION [dbo].[udf_IsComplex](@TableName as varchar)
RETURNS BIT
AS
BEGIN
    DECLARE @retVal bit;
    DECLARE @icount int;

SELECT @icount=COUNT(*)
FROM sys.columns AS c
INNER JOIN sys.objects AS o ON c.object_id=o.object_id
INNER JOIN sys.types AS t ON c.user_type_id=t.user_type_id
WHERE ((t.name IN ('text', 'ntext', 'image', 'xml')) OR (t.name IN ('varchar', 'nvarchar', 'varbinary') AND c.max_length=-1))
and o.name=@TableName;
  
        IF @icount = 0 
        BEGIN
            SET @retVal = 0 
        END
	ELSE
	BEGIN
	   SET @retVal = 1
	END

    RETURN @retVal
END

Open in new window


It checks the table for any complex data types and returns 1 if found any of the specified data types as column type annd returns 0 if nonw were found.

I checked the sql statement separately and it works fine, but now when I call the function from any database on the server as

select master.dbo.udf_IsComplex('Table_1');

it returns 0 even though Table_1 does contain type image and varchar(max)

can someone point out to me what am I doing wrong?
0
Comment
Question by:YZlat
  • 6
  • 3
  • 3
  • +1
13 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 39669811
>CREATE FUNCTION [dbo].[udf_IsComplex](@TableName as varchar)
  the input parameter is  accepting only 1 character  , change it to varchar(100)
0
 
LVL 16

Accepted Solution

by:
DcpKing earned 250 total points
ID: 39669841
You'll need to add the schema:

and o.name=@TableName
and o.schema_id = 1               --for example

Open in new window


and also the database (there's a different sys.objects for each database). This means that you'll have to wrap the whole thing up as a piece of dynamic SQL - sorry!

hth

Mike
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 39669899
Easiest might be to create the function in every db in which it will be used.

The function below allows, but does not require, the schema name to precede the table name.



CREATE FUNCTION [dbo].[udf_IsComplex] (
    @TableName AS varchar(257)
)
RETURNS bit
AS
--Sample uses:
--    SELECT dbo.udf_IsComplex('tablename')
--    SELECT dbo.udf_IsComplex('schema2.tablename')
BEGIN
RETURN (
    SELECT CASE WHEN EXISTS(SELECT 1
        FROM sys.columns AS c
        INNER JOIN sys.objects AS o ON c.object_id=o.object_id
        INNER JOIN sys.types AS t ON c.user_type_id=t.user_type_id
        WHERE ((t.name IN ('text', 'ntext', 'image', 'xml')) OR
               (t.name IN ('varchar', 'nvarchar', 'varbinary') AND c.max_length=-1))
               AND o.schema_id = (SELECT schema_id FROM sys.schemas s WHERE name=ISNULL(PARSENAME(@TableName, 2), 'dbo'))
               AND o.name=PARSENAME(@TableName, 1)
        ) THEN 1 ELSE 0 END
)
END
0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
LVL 35

Author Comment

by:YZlat
ID: 39669901
aneeshattingal, I changed it to varchar(100) but still returns 0
0
 
LVL 35

Author Comment

by:YZlat
ID: 39669923
The thing is i'd like to use this function as a part of dynamic SQL that runs for each database on the server
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 39669949
You can't use dynamic SQL in a function.  Naturally you can use dynamic SQL to invoke the function from within each db, although the function would have to exist in every db from which it's to be called/invoked.

I don't know of any way to have a function residing in one db to dynamically reference different dbs depending on how it's called, other than hard-coding each db name and using checks in the code to only run it for the db name/pattern passed in, and you may not want to do that.
0
 
LVL 35

Author Comment

by:YZlat
ID: 39669978
ScottPletcher, that's exactly what i am trying to do - use the function within my dynamic sql
0
 
LVL 35

Author Comment

by:YZlat
ID: 39669990
Here is my full dynamic SQL that calls IsComplex function:

WHILE @i <= @maxI
	BEGIN
	SET @SQL = ''
	print @i
	
	SELECT @SQL = @SQL + CASE WHEN LEN(@SQL) > 7500 THEN '' ELSE CHAR(13) + 'USE ' + QUOTENAME([name]) + ';
		SELECT ' +quotename([name],'''') + ' as DatabaseName, 
			SchemaName=ss.name,
			TableName= OBJECT_NAME (ps.object_id),
			IndexName=i.name, 
			[FragPercent]=ps.avg_fragmentation_in_percent,
			IsComplex=udf_IsComplex(OBJECT_NAME (ps.object_id)),
			[ActionTaken]=CASE WHEN ps.avg_fragmentation_in_percent > 30 THEN ' + quotename('Rebuild','''') + 
            ' ELSE ' + quotename('Reoraganize','''') + ' END
			FROM sys.dm_db_index_physical_stats(ISNULL(DB_ID(' + quotename([name],'''') + '),0),NULL, NULL, NULL, ' + quotename('DETAILED','''') + ') as ps
			INNER JOIN sys.indexes as i ON ps.[object_id] = i.[object_id] AND ps.index_id = i.index_id
			INNER JOIN sys.objects as o ON ps.object_id = o.object_id
			INNER JOIN sys.schemas as ss ON ss.[schema_id] = o.[schema_id]
			WHERE i.index_id != 0
			AND ps.database_id IN(SELECT database_id FROM sys.databases WHERE state_desc=' + quotename('ONLINE','''') + ')
			AND ps.avg_fragmentation_in_percent > 5; '  END
	FROM sys.databases  
	WHERE database_id = @i
	AND state_desc='ONLINE'
	AND compatibility_level > 80

SELECT @SQL
	INSERT ##IndexFragmentation 

	EXECUTE sp_executesql @SQL
	
	-- increment loop counter
	SELECT @i = MIN(database_id)
	FROM sys.databases  
	WHERE database_id > @i    
	AND state_desc='ONLINE'      
	AND compatibility_level > 80
 
END

Open in new window

0
 
LVL 16

Expert Comment

by:DcpKing
ID: 39669994
" i'd like to use this function as a part of dynamic SQL that runs for each database on the server"

then put it into the Model and it'll appear as you create new databases. Put it now into each database that already exists. Then call it from whereever you happen to be.

You can use a batch over different databases; you could use a procedure that switched from database to database (get the list from master.sys.databases into a table in a user database), running your code and storing the results into a table ...

hth

Mike
0
 
LVL 35

Author Comment

by:YZlat
ID: 39670043
I would like to avoid doing that and keep it in the master database. is that possible?

if not, is there a better way to find out if each table selected in the select above contains complex data type or not?
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 39670078
>> is that possible? <<

Not AFAIK.


>> is there a better way to find out if each table selected in the select above contains complex data type or not? <<

Not that I can think of right now.  A function would seem to be the preferred method.
0
 
LVL 35

Author Comment

by:YZlat
ID: 39670160
I think i came up with something:

I will dynamically loop through each database and use somethin glike that to retrieve necessary data:

SELECT 'DBName' as DatabaseName, 
			SchemaName=ss.name,
			TableName= OBJECT_NAME (ps.object_id),
			IndexName=i.name, 
			[FragPercent]=ps.avg_fragmentation_in_percent,
			IsComplex=(SELECT COUNT(*)
FROM sys.columns AS c1
INNER JOIN sys.objects AS o1 ON c1.object_id=o1.object_id
INNER JOIN sys.types AS t1 ON c1.user_type_id=t1.user_type_id
WHERE ((t1.name IN ('text', 'ntext', 'image', 'xml')) OR (t1.name IN ('varchar', 'nvarchar', 'varbinary') AND c1.max_length=-1)) AND o1.Name =OBJECT_NAME (ps.object_id)),
			[ActionTaken]=CASE WHEN ps.avg_fragmentation_in_percent > 30 THEN 'Rebuild' 
             ELSE 'Reorganize' END
			FROM sys.dm_db_index_physical_stats(ISNULL(DB_ID(' + quotename([name],'''') + '),0),NULL, NULL, NULL, 'DETAILED') as ps
			INNER JOIN sys.indexes as i ON ps.[object_id] = i.[object_id] AND ps.index_id = i.index_id
			INNER JOIN sys.objects as o ON ps.object_id = o.object_id
			INNER JOIN sys.schemas as ss ON ss.[schema_id] = o.[schema_id]
			WHERE i.index_id != 0
			AND ps.database_id IN(SELECT database_id FROM sys.databases WHERE state_desc='ONLINE')
			AND ps.avg_fragmentation_in_percent > 5;

Open in new window

0
 
LVL 16

Expert Comment

by:DcpKing
ID: 39670168
"I would like to avoid doing that and keep it in the master database. is that possible?"

I don't believe so.

However, you can write code that reaches out to each database after getting a list of the databases from master.sys.databases.
In this code you could interrogate the "local" sys.objects and other tables, as you do currently, to determine your answer.

It would certainly be a somewhat complicated piece of dynamic SQL, and you would have to keep the results in a static table somewhere.

So yes, it would be possible, but it'll take a a few hours to write and test, whereas putting the (working) function into Model and then cycling through the databases running the function inside a piece of code would probably be lots simpler to write.

Your choice!

hth

Mike
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

806 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question