SQL Server

How to Drop All Relations in A Database

Welcome Back! I hope you enjoy the content on this site. If you have not done so already, you may want to subscribe to my RSS feed or become a fan of this blog on Facebook. Thanks for visiting!

this is related to my earlier post on dropping all indexes from an sql database but this one is to delete all foreign key relations from a database.

set nocount on
 declare @statements cursor
 set @statements = cursor static for
 select  'alter table ' + quotename(ctu.table_schema) + '.' + quotename(ctu.table_name) +
         ' drop constraint ' + quotename(cc.constraint_name)
 from  INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS as cc
          join INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE as ctu
           on cc.constraint_catalog = ctu.constraint_catalog
              and cc.constraint_schema = ctu.constraint_schema
              and cc.constraint_name = ctu.constraint_name

 open @statements
 declare @statement nvarchar(1000)
 While  (1=1)
  begin
         fetch from @statements into @statement
                if @@fetch_status <> 0
                 break
                exec (@statement)
         end

Hope this was Helpful!

Hatim

How To Drop All Indexes From A Database

I was doing some performance tweaking of a batch job that was hanging and i was suspecting the indexes in the database to have something to do with it shocking up so I used this script to clear all indexes.

Ironically it was a missing index that caused the bottleneck. But here it is for anyone that might need it.

DECLARE @indexName VARCHAR(128)
DECLARE @tableName VARCHAR(128)

DECLARE [indexes] CURSOR FOR

    SELECT        [sysindexes].[name] AS [Index],
            [sysobjects].[name] AS [Table]

    FROM        [sysindexes]

    INNER JOIN    [sysobjects]
    ON        [sysindexes].[id] = [sysobjects].[id]

    WHERE        [sysindexes].[name] IS NOT NULL
    AND        [sysobjects].[type] = 'U'

OPEN [indexes]

FETCH NEXT FROM [indexes] INTO @indexName, @tableName

WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT 'DROP INDEX [' + @indexName + '] ON [' + @tableName + ']'

    FETCH NEXT FROM [indexes] INTO @indexName, @tableName
END

CLOSE        [indexes]
DEALLOCATE    [indexes]

Hope this was helpful!

Hatim

How To Change Numbers Format in Reporting Services

This one had me spinning for a while, so I am sure someone else would get some value out of this post.

Our client had a French version of Microsoft SQL Server 2005 and Reporting Services but on their reports the numbers format was using a comma as the thousands separator and the point as the decimal separator. They wanted it to use the French number formatting which is the space as the thousands separator and the comma as the decimal separator.

I started by looking at the Reporting Services Options and then SQL Server options but I couldn’t find anything to set the locale or number formatting.

So I started looking at the report properties in Visual Studio and there it was:

You can change it to French or whichever language you are using and the number format would follow.

Hope this helps.

Hatim