Archive

Archive for January, 2010

Script to drop all objects of a Schema in SQL Server

January 31, 2010 8 comments

 

I have spent almost 3 hours to complete the script and test it for couple of sample scenarios during this weekend (31/01/10).  The script initially drops all the objects of the schema and then drops the schema itself. It takes care of all the object dependencies with in the schema by dropping all of them in a specific order that will resolve the dependency issues.

Download the SP created using the script from here (MS Word Document) or use this Google Doc link to view the script(Thanks to Elias for the link).

The stored procedure takes two parameters SchemaName and the WorkTest. Use WorkTest equal to ‘t’ to print all the drop statements without executing them or specify anything else to execute the drop operations. The default option is ‘w’ i.e. Work for me.

EXEC CleanUpSchema 'MySchema', 't'        -- debug
GO
EXEC CleanupSchema 'MySchema', 'w'        -- work for me

 

These are the known limitations of the script

  • It can not drop a PK table in the schema with an XML index or Spatial index defined
  • It can not drop the schema which is referred by a XML schema collection
    Please let me know if you find any more issues with the script. I will list all of them here for others reference and will fix them to improve it further.

 

Hope it helps

🙂 ranjith 🙂

Advertisements

How to get the File Group of a Table or an Index?

January 31, 2010 2 comments

 

The file group information of both an Index and a Table are stored in the sys.indexes table. You might wonder how the tables file group is stored in sys.indexes metadata table. It is because when ever a clustered index is created on a table in SQL Server the physical data pages of the table will be made part of the clustered index i.e. the leaf nodes of the clustered index contain the physical table data. So the clustered index file group is the file group of the table.

If there is no clustered index on the table then it is represented as a HEAP with index_id equal to ZERO in sys.indexes table. 

At any point a clustered index with index_id equal to 1 or a heap with index_id equal to ZERO exists for a table in sys.indexes table.  All non clustered indexes will have index_id greater than 1.

Below query gets the file group of the table Employee in HumanResources schema of AdventureWorks database.

 SELECT d.name AS FileGroup
 FROM sys.filegroups d
 JOIN sys.indexes i
   ON i.data_space_id = d.data_space_id
 JOIN sys.tables t
   ON t.object_id = i.object_id
WHERE i.index_id<2                     -- could be heap or a clustered table
 AND t.name= 'Employee'
 AND t.schema_id = schema_id('HumanResources')

And below query gets the file group of the index ‘AK_Employee_rowguid’ on Employee table in HumanResources schema of AdventureWorks database.

 SELECT d.name AS FileGroup
 FROM sys.filegroups d
 JOIN sys.indexes i
   ON i.data_space_id = d.data_space_id
 JOIN sys.tables t
   ON t.object_id = i.object_id
WHERE i.name = 'AK_Employee_rowguid'        
 AND t.name= 'Employee'
 AND t.schema_id = schema_id('HumanResources')

Please note that schema_id is important otherwise both the queries might return multiple results if you have same table name on different schemas.

Hope it helps

🙂 ranjith 🙂

Understanding SET QUOTED_IDENTIFIER ON/OFF and SET ANSI_NULLS ON/OFF

January 10, 2010 12 comments

These are the two widely used SET options in SQL Server. Most developers explicitly set these options while creating Stored Procedures, Triggers and User Defined Functions but many are unclear on why we need to explicitly SET them? And why they are special compared to other options?

Below is the typical usage of these options.

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE SampleProcedure
AS
BEGIN
 -- select employees
 SELECT * FROM HumanResources.Employee
END

Lets first understand what they exactly mean to SQL Server and then we will move on to why they are special.

SET QUOTED_IDENTIFIER ON/OFF:

It specifies how SQL Server treats the data that is defined in Single Quotes and Double Quotes.

When it is set to ON any character set that is defined in the double quotes “” is treated as a T-SQL Identifier (Table Name, Proc Name, Column Name….etc) and the T-SQL rules for naming identifiers will not be applicable to it. And any character set that is defined in the Single Quotes ‘’ is treated as a Literal.

SET QUOTED_IDENTIFIER ON
CREATE TABLE "SELECT" ("TABLE" int)  -- SUCCESS

GO

SET QUOTED_IDENTIFIER ON
SELECT "sometext" AS Value   -- FAIL because “sometext” is not a literal 

Though the SELECT” and “TABLE” are reserved keywords  we are able to create the table because they are now treated as identifiers and the T SQL rules for identifier names are ignored.

When it is set to OFF any character set that is defined either in Single Quotes or in Double Quotes is treated as a literal.

SET QUOTED_IDENTIFIER OFF
CREATE TABLE "SELECT"(TABLEint) -- FAIL

GO

SET QUOTED_IDENTIFIER OFF
SELECT "sometext" AS Value    -- SUCCESS as “sometext” is treated as a literal

You can clearly see the difference in CREATE TABLE and SELECT query. Here the CREATE TABLE fails because “SELECT” is a reserved keyword and it is considered as a literal.
The default behaviour is ON in any database.
SET ANSI_NULLS ON/OFF:

The ANSI_NULLS option specifies that how SQL Server handles the comparison operations with NULL values. When it is set to ON any comparison with NULL using = and <> will yield to false value. And it is the ISO defined standard behavior. So to do the comparison with NULL values we need to use IS NULL and IS NOT NULL.
And when it is set to OFF any comparison with NULL using = and <> will work as usual i.e. NULL = NULL returns true and 1= NULL returns false.
SET ANSI_NULLS ON
IF NULL = NULL
 PRINT 'same'
ELSE
 PRINT 'different'
--result:  different

SET ANSI_NULLS ON
IF NULL IS NULL
 PRINT 'same'
ELSE
 PRINT 'different'
-- result: same 

The default behaviour is ON in any database. As per BOL 2008 this option will always be set to ON in the future releases of SQL Server and any explicit SET to OFF will result an error. So avoid explicitly setting this option in future development work.

Why are these two options Special?:

These two SET options are special because whenever a stored procedure or a Trigger or a User Defined Function is created or modified with these options explicitly SET SQL Server remembers those settings. And every time the stored procedure is executed it always uses the stored settings irrespective of what the current user session settings are. So the behaviour of the stored procedure is not altered by the calling session settings and the usage of the SET option behaviour inside the SP is always guaranteed.

You can get any procedure or trigger or function settings for these options from the sys.sql_modules metadata table.

SELECT uses_ansi_nulls, uses_quoted_identifier
 FROM sys.sql_modules
WHERE object_id = object_id('SampleProcedure')

And if you need to guarantee the behaviour for other SET options like SET ARITHABORT inside the SP then you need to SET them inside the procedure. The scope of the options specified inside the procedure is only applicable until the procedure completes its execution.

Hope it helps.

🙂 ranjith 🙂