[dba-SQLServer] This is a cute little gotcha

Abbreviated code, enough to give the gist without the gory details....

<sql>
DECLARE @statement VARCHAR(255)
-- Build Cursor of Alter Index Statements
DECLARE Statement_Cursor CURSOR
FOR SELECT
'UPDATE STATISTICS [' + SCHEMA_NAME() + '].[' + sysobjects.NAME + ']'
FROM sysobjects
WHERE TYPE = 'U' -- user tables
OPEN Statement_Cursor
FETCH NEXT FROM Statement_Cursor INTO @statement
etc.
</sql>

This code generates a series of statements like:

<sql>
UPDATE STATISTICS [dbo].[BESTAction]
</sql>

What is interesting is that this construction calls Schema_Name() just once
and it returns "dbo". But this is not appropriate for a db such as
AdventureWorks which is subdivided into many schemas.

I notice in the sysobjects table that there is a column called uid which
corresponds to the schemas but I don't know where it points. That would seem
a workable solution to this problem.

Arthur