Rename Database

USE master

GO

EXEC sp_dboption Fox8, 'Single User', True

GO

-- if this command fails, use:

-- alter database Fox8 set single_user with rollback IMMEDIATE

-- see: http://copypastenet.blogspot.com/2011/01/spdboption-single-user-command-failed.html

EXEC sp_renamedb 'Fox8', 'Fox8NotUnicode'

GO

EXEC sp_dboption Fox8NotUnicode, 'Single User', False

GO

sp_dboption 'Single User' command failed

Problem:

USE master

GO

EXEC sp_dboption DBNAME, 'Single User', True

GO

Msg 5070, Level 16, State 2, Line 1

Database state cannot be changed while other users are using the database 'DBNAME'

Msg 5069, Level 16, State 1, Line 1

ALTER DATABASE statement failed.

sp_dboption command failed.

Solution:

Use this command:

alter database DBNAME set single_user with rollback IMMEDIATE

GO

Converting int to datetime

Converting int to datetime nean to add days as the int number to 1900-01-01 00:00:00.

What nean:

cast(37947 as datetime) = dateadd(day, 37947, '19000101 00:00:00') 

[=20031124 00:00:00]

and:

select cast(0 as datetime) = 1900-01-01 00:00:00

Converting datetime to ing will return the number of the days since 1900-01-01 00:00:00:

select cast(cast('20031124 00:00:00' as datetime) as int) = 37947

BTW:

select cast(37947.5 as datetime) will return 2003-11-24 12:00:00.000

Time convertion: EST and EDT to GMT

EST: Eastern Time Zone is the Eastern Time Zone of the United States of America (USA) and Canada

GMT = EST+5

EDT: Atlantic Daylight Time is the Atlantic Time Zone of the United States of America (USA) and Canada

GMT = EST+4

http://wwp.greenwichmeantime.com/time-zone/usa/eastern-time/convert/

Mssql 2008 R2 Restore error: File '.ndf' is claimed by (4) and (3). The WITH MOVE clause can be used to relocate one or more files

Mssql 2008 R2 Restore Error: 

File '.ndf' is claimed by (4) and (3). The WITH MOVE clause can be used to relocate one or more files.

Solutions:

1.
Restore behavior for new DB was changed in SQL 2008:

In 2005, we can create new (empty) DB and restore from bak file to the new DB.

In 2008, Restore from bak file to new DB (that was not created yet).
(Right click on Databases-->Restore Database...)

Another possible causes of the problem can be (not only for MSSQL 2008):
2.
 Different number of files between the new DB and the source DB.

3.
Attempting to use a file for more than one purpose.

Find text in columns content

Find text in columns content of the tables in the DB.

If you want to replace the text with another text - remove remarks from the remarked lines.

begin tran

SET NOCOUNT ON 

DECLARE @stringToFind VARCHAR(100) 

--DECLARE @stringToReplace VARCHAR(100) 

DECLARE @schema sysname 

DECLARE @table sysname 

DECLARE @count INT 

DECLARE @sqlCommand VARCHAR(8000) 

DECLARE @where VARCHAR(8000) 

DECLARE @columnName sysname 

DECLARE @object_id INT 

                     

SET @stringToFind = 'admin' 

--SET @stringToReplace = 'Jones' 

                        

DECLARE TAB_CURSOR CURSOR  FOR 

SELECT   B.NAME      AS SCHEMANAME, 

         A.NAME      AS TABLENAME, 

         A.OBJECT_ID 

FROM     sys.objects A 

         INNER JOIN sys.schemas B 

           ON A.SCHEMA_ID = B.SCHEMA_ID 

WHERE    TYPE = 'U' 

ORDER BY 1 

          

OPEN TAB_CURSOR 

FETCH NEXT FROM TAB_CURSOR 

INTO @schema, 

     @table, 

     @object_id 

      

WHILE @@FETCH_STATUS = 0 

  BEGIN 

    DECLARE COL_CURSOR CURSOR FOR 

    SELECT A.NAME 

    FROM   sys.columns A 

           INNER JOIN sys.types B 

             ON A.SYSTEM_TYPE_ID = B.SYSTEM_TYPE_ID 

    WHERE  OBJECT_ID = @object_id 

           AND IS_COMPUTED = 0 

           AND B.NAME IN ('char','nchar','nvarchar','varchar','text','ntext') 

    OPEN COL_CURSOR 

     

    FETCH NEXT FROM COL_CURSOR 

    INTO @columnName 

     

    WHILE @@FETCH_STATUS = 0 

      BEGIN 

print @schema + '.' + @table + '.' + @columnName

----------------------------------------------------

-- only search:

        EXEC (' if exists (select [' + @columnName + '] from [' + @schema + '].[' + @table + '] where [' + @columnName + '] like ''%' + @stringToFind + '%'') ' +

'select [' + @columnName + '] as ' + @schema + '_' + @table + '_' + @columnName +

' from [' + @schema + '].[' + @table + '] where [' + @columnName + '] like ''%' + @stringToFind + '%''')

----------------------------------------------------

-- search/replace:

        --SET @sqlCommand = 'UPDATE ' + @schema + '.' + @table + ' SET [' + @columnName + '] = REPLACE(convert(nvarchar(max),[' + @columnName + ']),''' + @stringToFind + ''',''' + @stringToReplace + ''')' 

        --SET @where = ' WHERE [' + @columnName + '] LIKE ''%' + @stringToFind + '%''' 

        --EXEC( @sqlCommand + @where) 

        --SET @count = @@ROWCOUNT 

        --IF @count > 0 

        --BEGIN 

            --PRINT @sqlCommand + @where 

            --PRINT 'Updated: ' + CONVERT(VARCHAR(10),@count) 

--print @schema + '.' + @table + '.' + @columnName

        --END 

----------------------------------------------------

         

        FETCH NEXT FROM COL_CURSOR 

        INTO @columnName 

      END 

     

    CLOSE COL_CURSOR 

    DEALLOCATE COL_CURSOR 

     

    FETCH NEXT FROM TAB_CURSOR 

    INTO @schema, 

         @table, 

         @object_id 

  END 

   

CLOSE TAB_CURSOR 

DEALLOCATE TAB_CURSOR

rollback tran

Allows/Don't allow explicit values to be inserted into identity column

-- allow:

set identity_insert dbo.TableA on

-- don't allow:

set identity_insert dbo.TableA off

DB upgrade to 2008R2 checklist

Before upgrade:

- Backup database

- Run Upgrade Advisor

Run upgrade advisor for all databases (including system databases) and fix any issue/blocker for upgrade.

http://msdn.microsoft.com/en-us/library/ms144256.aspx

http://geekswithblogs.net/akshaylamba/archive/2010/12/22/overview-of-microsoft-sql-server-2008-upgrade-advisor.aspx

Error fix: SQL Server 2008 Upgrade Advisor fails to connect to an instance:

http://social.msdn.microsoft.com/Forums/en/sqldataaccess/thread/cfb5003a-a039-49b5-a94a-2699c03cc33a

I Skipped this error by enter "default" to the named instance.

After upgrade:

- Update statistics (to ensure performance)

sp_updatestats

- Check Collation

- Check compatibility level

Set compatibility level to 100, by the DB Properties window or by command.

EXEC sp_dbcmptlevel DBNAME, 100

- Updating page or row counts or both for all objects in the current database

DBCC updateusage 0 -- 0 = current DB

http://technet.microsoft.com/en-us/library/ms188414.aspx

- Recreate full text indexes removed from the databases.

- Re-build Full-Text catalogs

ALTER FULLTEXT CATALOG [CATALOGNAME] REBUILD

- Test the application with the new database server 2008.

- It's recommended to check definitions of Users, Schemas, Rules in the new server.

More links:

http://msdn.microsoft.com/en-us/library/bb677622.aspx

http://technet.microsoft.com/en-us/library/ms144267.aspx

http://www.sqlservercentral.com/articles/Upgrade/65872/

clr enabled

clr enabled - allow running .NET code from SQL

Enabling CLR Integration:

sp_configure 'clr enabled', 1;

GO

RECONFIGURE;

GO

sp_configure

go

List all modules where an "execute as" has been specified

select object_name(object_id) from sys.sql_modules where execute_as_principal_id = user_id('Lms')

GO

SELECT  objects.type_desc                       AS ObjectTypeDescription

,               objects.type                            AS ObjectTypeCd

,               SCHEMA_NAME(objects.SCHEMA_ID) AS SchemaName

,               objects.name                            AS ObjectName

,               USER_NAME(sql_modules.execute_as_principal_id) ExecAsUserName

,               objects.SCHEMA_ID

,               sql_modules.execute_as_principal_id

FROM    sys.objects                                     AS objects

JOIN    sys.sql_modules                         AS sql_modules

                ON sql_modules.OBJECT_ID        = objects.OBJECT_ID 

WHERE   sql_modules.execute_as_principal_id  IS NOT NULL -- Exec As is not "owner"

--AND             sql_modules.execute_as_principal_id <> COALESCE(objects.principal_id , objects.SCHEMA_ID )

GO

Copy few files Content into one file

C:\Documents and Settings\itaig>copy "C:\Projects\\Scripts\Stored Procedures\*.*" "C:\Projects\Scripts\Stored Procedures\a.sql"

SQL Server Parameter Sniffing

When a stored procedure is compiled for the first time, the values of the parameters supplied with the execution call are used to optimize the statements within that stored procedure. 

Parameter Sniffing occurs when the first execution has atypical parameter values (comparative to the expected values).
Actually, Parameter sniffing occurs every time a procedure is compiled - issues may arise when you want to get different query plans for different parameter values...

Note: Basically, Parameter sniffing is not bad thing... Only when the query plan was set by unexpected values.

http://jahaines.blogspot.com/2010/08/sql-server-parameter-sniffing.html

Find out Parameter Sniffing

There are few options to find out Parameter Sniffing, but no one is absolute.

1. WITH RECOMPILE:

Add 'WITH RECOMPILE' to the procedure and see If the problem disappears. If yes - the problem is related to Parameter Sniffing.

2. Different query plans:

Check if there are different query plans. Use profiler or DMVs. Check if there are different database users or session settings.

3. Inconsistent execution times:

Look for procedures that have inconsistent execution times.

Example:

---------------------------------------------------------------------------

-- simple query without parameters

-- no parameters, no Parameter sniffing :-)

-- a lot of rows - index scan, few rows - index seek

DBCC freeproccache

go

SELECT * FROM dbo.SomeTable

where ColumnInt between 100000 and 100100 --return 81 rows

go

SELECT * FROM dbo.SomeTable

where ColumnInt between 100 and 100100 --return 44710 rows

go

DBCC freeproccache

go

SELECT * FROM dbo.SomeTable

where ColumnInt between 100 and 100100 --return 44710 rows

go

SELECT * FROM dbo.SomeTable

where ColumnInt between 100000 and 100100 --return 81 rows

go

---------------------------------------------------------------------------

-- execute query with parameters

-- Parameter sniffing:

-- first query return a lot of rows - index scan for the 2 queries

-- first query return few rows - index seek for the 2 queries

DBCC freeproccache

GO

EXEC sp_executesql N'SELECT * FROM dbo.SomeTable where ColumnInt between @ColumnInt and @ColumnIntMax',N'@ColumnInt INT, @ColumnIntMax  INT',@ColumnInt=100000,@ColumnIntMax=100100

GO

EXEC sp_executesql N'SELECT * FROM dbo.SomeTable where ColumnInt between @ColumnInt and @ColumnIntMax',N'@ColumnInt INT, @ColumnIntMax  INT',@ColumnInt=100,@ColumnIntMax=100100

GO

DBCC freeproccache

GO

EXEC sp_executesql N'SELECT * FROM dbo.SomeTable where ColumnInt between @ColumnInt and @ColumnIntMax',N'@ColumnInt INT, @ColumnIntMax  INT',@ColumnInt=100,@ColumnIntMax=100100

GO

EXEC sp_executesql N'SELECT * FROM dbo.SomeTable where ColumnInt between @ColumnInt and @ColumnIntMax',N'@ColumnInt INT, @ColumnIntMax  INT',@ColumnInt=100000,@ColumnIntMax=100100

GO

---------------------------------------------------------------------------

-- execute query with parameters

-- Parameter sniffing as above:

-- first query return a lot of rows - index scan for the 2 queries

-- first query return few rows - index seek for the 2 queries

CREATE PROCEDURE dbo.ItaiParameterSniffing

@ColumnInt INT, 

@ColumnIntMax  INT

as 

SELECT * FROM dbo.SomeTable 

where ColumnInt between @ColumnInt and @ColumnIntMax

GO

DBCC freeproccache

GO

exec dbo.ItaiParameterSniffing 100, 100100

go

exec dbo.ItaiParameterSniffing 100000, 100100

go

DBCC freeproccache

GO

exec dbo.ItaiParameterSniffing 100000, 100100

go

exec dbo.ItaiParameterSniffing 100, 100100

go

DROP PROCEDURE dbo.ItaiParameterSniffing

GO

---------------------------------------------------------------------------

Partition Schemes

SELECT *

FROM SYS.PARTITION_RANGE_VALUES prv

left join SYS.PARTITION_SCHEMES ps on ps.function_id = prv.function_id

left join SYS.DATA_SPACES ds on ds.data_space_id = ps.data_space_id

SELECT * FROM sys.partitions 

WHERE [object_id] = object_id('Sales') --If there are indexes on the table, each index will be one row in the partitions table

SELECT * FROM sys.partition_functions

SELECT * FROM sys.partition_schemes

SELECT * FROM sys.partition_range_values

http://www.sqlservercentral.com/articles/partition/64740/#

Create Partition Scheme

-- 0. create filegroup and file - if new filegroup/file are required

ALTER DATABASE GlobalSales ADD FILEGROUP PartitionFG

ALTER DATABASE GlobalSales ADD FILE

(

NAME = N'BlaBla' ,

FILENAME = N'C:\FolderA\FolderB\BlaBla.ndf'

)

TO FILEGROUP PartitionFG

-- 1. create partition function

CREATE PARTITION FUNCTION pfMonPartition (datetime)

AS RANGE RIGHT FOR VALUES ( '1 jan 2009', '1 feb 2009', '1 mar 2009')

--2. create partition scheme

CREATE PARTITION SCHEME SalesPartitionScheme

AS PARTITION pfMonthlyPartition

TO ( [PRIMARY], [PRIMARY], [PartitionFG2]) -- to FileGroups

http://msdn.microsoft.com/en-us/library/ms179854.aspx

------------------------------

-- new table

------------------------------

-- 3. create table on the scheme using specific column as a key

CREATE TABLE dbo.[Sales](

[SalesID] [int] NOT NULL,
.........

[DateOfSale] [datetime] NOT NULL

) ON [SalesPartitionScheme](DateOfSale)

------------------------------

-- exists table
http://social.technet.microsoft.com/Forums/he-IL/sqlhe/thread/e4271d96-2aae-449b-9649-aa96cf87ed6e

------------------------------

-- 3. drop indexes and constraints on the key column

DROP INDEX [IX_Sales_DateOfSale] ON [dbo].[Sales] WITH ( ONLINE = OFF )

GO

ALTER TABLE [dbo].[Sales] DROP CONSTRAINT [PK_Sales]

GO

-- 4. re-create the indexes and the constraints of the key column on the scheme

CREATE CLUSTERED INDEX [IX_Sales_DateOfSale] ON [dbo].[Sales]

(

[DateOfSale] ASC

)WITH ( SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = OFF)

ON [SalesPartitionScheme]([DateOfSale])

GO

ALTER TABLE [dbo].[Sales] ADD  CONSTRAINT [PK_Sales] PRIMARY KEY NONCLUSTERED

(

[SalesOrderID] ASC,

[DateOfSale] DESC

)WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON SalesPartitionScheme([DateOfSale])

Update statistics

exec sp_updatestats

go

http://msdn.microsoft.com/en-us/library/aa260337(v=sql.80).aspx

UPDATE STATISTICS TableName WITH SAMPLE 25 PERCENT, ALL

Go

http://msdn.microsoft.com/en-us/library/aa260645(v=sql.80).aspx

Get information about statistics

Exec SP_HelpStats @TableName, 'ALL'

Select         Object_Name(Object_Id) Tbl,

                Stats_Date(Object_Id, stats_id) As [Stats_Date],

                *

From        sys.stats

Where        Object_Id = Object_Id(@TableName);

Select        *,

                Object_Name(Object_Id) Tbl,

                Stats_Date(Object_Id, index_id) As [Stats_Date]

From        sys.indexes

Where        Object_Id = Object_Id(@TableName);

Select        Object_name(id),

                rowmodctr,

                *

From        sysindexes

Where        Object_name(id)=@TableName

DBCC Show_Statistics(@TableName,@ColumnName) With Histogram

Check if statistics are active in a DB

-- check if statistics are active

Select name, is_auto_create_stats_on,is_auto_update_stats_on From sys.databases Where database_id=db_id();

-- set statistics to be active

ALTER DATABASE @DBName SET AUTO_UPDATE_STATISTICS On;

ALTER DATABASE @DBName set AUTO_CREATE_STATISTICS On;

Go

Find tables without clustered index

SELECT o.name 

FROM sys.objects o

LEFT JOIN sys.indexes i ON o.object_id = i.object_id AND i.type_desc = 'CLUSTERED'

WHERE o.type='U'

AND i.type_desc IS NULL

Get blocking information

SELECT t1.resource_type,

        t1.resource_database_id,

        t1.resource_associated_entity_id,

        t1.request_mode,

        t1.request_session_id,

        t2.blocking_session_id

FROM sys.dm_tran_locks as t1

INNER JOIN sys.dm_os_waiting_tasks as t2 

ON t1.lock_owner_address = t2.resource_address

http://msdn.microsoft.com/en-us/library/ms190345.aspx

Get lock information

SELECT resource_type, resource_associated_entity_id,
        request_status, request_mode,request_session_id,
resource_description
FROM sys.dm_tran_locks
WHERE resource_database_id = 8

http://msdn.microsoft.com/en-us/library/ms190345.aspx

Cannot bulk load because the file ... could not be opened. Operating system error code 3(error not found).

Error Message:

Cannot bulk load because the file "C:\SQL\some_xml.xml" could not be opened. Operating system error code 3(error not found).

The SQL couldn't get to the file.

Probably the path is not a path on the Server machine, or a path that known to the server.

C = C driver on the server.

Import XML into SQL Server

The XML file (C:\SQL\some_xml.xml):

For this Type of XML:

note: C = C driver on the server.

SQL:

--  table to insert the data

CREATE TABLE #XML_Info (

   ColumnA varchar(100),

   ColumnB varchar(100), 

   ColumnInt numeric(5,2)

)

DECLARE @XMLData TABLE (XMLData XML)

INSERT INTO @XMLData

SELECT *

FROM OPENROWSET(BULK N'C:\SQL\some_xml.xml', SINGLE_BLOB) rs

select * from @XMLData

INSERT INTO #XML_Info (ColumnA, ColumnB, ColumnInt)

SELECT ColumnA = x.data.value('COLUMNA[1]','varchar(100)'),

       ColumnB = x.data.value('COLUMNB[1]','varchar(100)'),

       ColumnInt = x.data.value('COLUMNINT[1]','numeric(5,2)')

FROM @XMLData t

CROSS APPLY t.XMLData.nodes('/ROOTTAG/PARTAG') x(data)

SELECT * FROM #XML_Info

drop table #XML_Info

Result:

For this Type of XML:

<rows>
<row ColumnA="25759" ColumnB="171639" />
<row ColumnA="25762" ColumnB="171639" />
<row ColumnA="25763" ColumnB="171639" />
<row ColumnA="302665" ColumnB="152887" />

</rows>


DECLARE @MyTable TABLE 
( ColumnA int, ColumnB int)
DECLARE @XMLData1 TABLE (XMLData XML)
DECLARE @XML1 XML, @hDoc1 int  

INSERT INTO @XMLData1
SELECT * FROM OPENROWSET(BULK N'C:\SQL\some_xml.xml', SINGLE_BLOB) rs
SELECT @XML1 = XMLData from @XMLData1
--SELECT @XML1
exec sp_xml_preparedocument @hDoc1 output,@XML1

INSERT INTO @MyTable (ColumnA, ColumnB)
select *   
from OPENXML(@hDoc1,'/rows/row') with (  
ColumnA int '@ColumnA',   
ColumnB int '@ColumnB'
)  

exec sp_xml_removedocument @hDoc1  

Reverse of a string value

Reverse(String_Expression)

http://msdn.microsoft.com/en-us/library/ms180040.aspx

Right/Left of string expression

Returh the right/left size of a string expression, in length of int_Length:

Right (String_Expression, int_Length)

Left(String_Expression, int_Length)

Find Unused Objects in the DB

CREATE PROCEDURE  [dbo].[Itai_checkUnusedObjectsSP]
@IncludeTables int,
@IncludeViews int,
@IncludeFunctions int,
@IncludeSPs int,
@Schema nvarchar(100) = '',
@NotSchema nvarchar(100) = '',
@NamePrefix nvarchar(100) = ''
AS

declare @TempObjects table (schema_ nvarchar(50), table_ nvarchar(250))
declare @ObjectsNotToDelete table (schema_ nvarchar(50), table_ nvarchar(250))
declare @SQL as nvarchar(2000)
DECLARE @TempTypesStr nvarchar(100)
SET @TempTypesStr = ''

IF @IncludeTables+@IncludeViews+@IncludeFunctions+@IncludeSPs = 0
begin
select 'Select object type'
return
end


-- insert relevant objects into @TempObjects
SET @SQL = 'SELECT  SCHEMA_NAME(schema_id), name
FROM sys.objects
WHERE '

IF @IncludeTables = 1 SET @TempTypesStr = @TempTypesStr + ',''U'''
IF @IncludeViews = 1 SET @TempTypesStr = @TempTypesStr + ',''V'''
IF @IncludeFunctions = 1 SET @TempTypesStr = @TempTypesStr + ',''AF'',''FN'',''FS'',''FT'',''IF'',''TF'''
IF @IncludeSPs = 1 SET @TempTypesStr = @TempTypesStr + ',''P'',''PC'',''RF'',''X'''
select @TempTypesStr = stuff(@TempTypesStr,1,1,'')

SET @SQL = @SQL +
'type in (' + @TempTypesStr + ') '

IF @Schema <> '' SET @SQL = @SQL + '
AND SCHEMA_NAME(schema_id) = ''' + @Schema + ''' '

IF @NotSchema <> '' SET @SQL = @SQL + '
AND SCHEMA_NAME(schema_id) <> ''' + @NotSchema + ''' '

IF @NamePrefix <> '' SET @SQL = @SQL + '
AND name like ''' + @NamePrefix + '%'' '

print @SQL
insert into @TempObjects(schema_, table_) EXEC(@SQL)
--check:
--select * from @TempObjects

IF @NotSchema = ''
BEGIN
insert into @ObjectsNotToDelete(schema_, table_)
-- return objects that have conections
SELECT distinct t.schema_, t.table_ --, s.SPECIFIC_NAME as object_name_ --s.*
FROM INFORMATION_SCHEMA.ROUTINES s
inner join @TempObjects t on s.ROUTINE_DEFINITION LIKE '%' + t.table_ + '%'
--and s.SPECIFIC_SCHEMA = t.schema_
where t.table_ <> s.SPECIFIC_NAME
union
select distinct t.schema_, t.table_ --, object_name(s.id) as object_name_ --, count(*) as count_
from syscomments s inner join sys.objects o on o.object_id = s.id
inner join @TempObjects t on s.text like  '%' + t.table_ + '%' --and SCHEMA_NAME(o.schema_id) = t.schema_
where t.table_ <> object_name(s.id)
--order by table_
END
ELSE
BEGIN
insert into @ObjectsNotToDelete(schema_, table_)
-- return objects that have conections
SELECT distinct t.schema_, t.table_ --, s.SPECIFIC_NAME as object_name_ --s.*
FROM INFORMATION_SCHEMA.ROUTINES s
inner join @TempObjects t on s.ROUTINE_DEFINITION LIKE '%' + t.table_ + '%'
--and s.SPECIFIC_SCHEMA = t.schema_
and s.SPECIFIC_SCHEMA <> @NotSchema
where t.table_ <> s.SPECIFIC_NAME
union
select distinct t.schema_, t.table_ --, object_name(s.id) as object_name_ --, count(*) as count_
from syscomments s inner join sys.objects o on o.object_id = s.id
inner join @TempObjects t on s.text like  '%' + t.table_ + '%' and SCHEMA_NAME(o.schema_id) <> @NotSchema
where t.table_ <> object_name(s.id)
--order by table_
END

if @IncludeTables = 1
begin
IF @NotSchema = ''
BEGIN
insert into @ObjectsNotToDelete(schema_, table_)
select SCHEMA_NAME(o.schema_id),
--f.constraint_object_id, object_name(f.constraint_object_id) as constraint_object, o.name,
--f.constraint_column_id, --object_name(f.constraint_column_id) as constraint_column,
--f.parent_object_id, object_name(f.parent_object_id) as parent_object,
--f.parent_column_id, object_name(f.parent_column_id) as parent_column,
--f.referenced_object_id,
object_name(f.referenced_object_id) as referenced_object --,
--f.referenced_column_id --, object_name(f.referenced_column_id) as referenced_column
from sys.foreign_key_columns f
inner join sys.objects o on o.object_id = f.referenced_object_id --and SCHEMA_NAME(o.schema_id) <> 'Lms'
where object_name(referenced_object_id) like @NamePrefix + '%'
END
ELSE
BEGIN
insert into @ObjectsNotToDelete(schema_, table_)
select SCHEMA_NAME(o.schema_id),
--f.constraint_object_id, object_name(f.constraint_object_id) as constraint_object, o.name,
--f.constraint_column_id, --object_name(f.constraint_column_id) as constraint_column,
--f.parent_object_id, object_name(f.parent_object_id) as parent_object,
--f.parent_column_id, object_name(f.parent_column_id) as parent_column,
--f.referenced_object_id,
object_name(f.referenced_object_id) as referenced_object --,
--f.referenced_column_id --, object_name(f.referenced_column_id) as referenced_column
from sys.foreign_key_columns f
inner join sys.objects o on o.object_id = f.referenced_object_id and SCHEMA_NAME(o.schema_id) <> @NotSchema
where object_name(referenced_object_id) like @NamePrefix + '%'
END
end

------------------------------------------------------------------------------------
-- For this section, you have to create temporary table in the database, that will contain the objects that you know that you can't delete (For example: object that is not called from the DB, but do called from the Application).
-- The SP do not display those objects in the results list.
-- See below the table script.
IF @IncludeTables = 1
insert into @ObjectsNotToDelete(schema_, table_)
select schema_, objName_ from CheckedObjectsNotToDelete where type_ = 'Table'
IF @IncludeViews = 1
insert into @ObjectsNotToDelete(schema_, table_)
select schema_, objName_ from CheckedObjectsNotToDelete where type_ = 'View'
IF @IncludeFunctions = 1
insert into @ObjectsNotToDelete(schema_, table_)
select schema_, objName_ from CheckedObjectsNotToDelete where type_ = 'Function'
IF @IncludeSPs = 1
insert into @ObjectsNotToDelete(schema_, table_)
select schema_, objName_ from CheckedObjectsNotToDelete where type_ = 'SP'
------------------------------------------------------------------------------------


--checks:
--select * from @TempObjects order by schema_, table_
--select distinct * from @ObjectsNotToDelete order by schema_, table_

select o.* from @TempObjects o
left join (select distinct * from @ObjectsNotToDelete) ontd on o.schema_ = ontd.schema_ and o.table_ = ontd.table_
where ontd.table_ is null
order by o.schema_, o.table_

GO


------------------------------------------------------------------------------------
--Script to create table CheckedObjectsNotToDelete:
CREATE TABLE [dbo].[CheckedObjectsNotToDelete](
[type_] [nvarchar](50) NULL,
[schema_] [nvarchar](50) NULL,
[objName_] [nvarchar](250) NULL
) ON [PRIMARY]

GO

-- type_:
-- Table
-- View
-- Function
-- SP (stored procedure)

Recompile stored procedure

sp_recompile:

EXEC sp_recompile N'MyProcedure'


http://msdn.microsoft.com/en-us/library/ms181647.aspx

inner join .... select top 1

Instead query like this:

SELECT TOP 1 * FROM Customers c (NOLOCK) WHERE Key = (mykey)

AND NOT EXISTS (SELECT 0 FROM Invoices i (NOLOCK) WHERE i.key = c.key AND i.Date > GETDATE() )

try to use this syntax:

select * from Customers inner join (select top 1 PK from ... order by ..) X on Customers.PK = X.PK

Usually it will behave better than select top 1 * from ...

Make Drop statements for table deletion

-----------------------------------------------------------------------
-- execute as SP:

CREATE PROCEDURE  [dbo].[Itai_MakeStatementsForDropTable] 

@TableName nvarchar(250),

@SchemaName nvarchar(50),

@ShowDetailes bit = 1

AS

-----------------------------------------------------------------------
-- execute as script:

--declare @TableName nvarchar(250), @SchemaName nvarchar(50), @ShowDetailes bit

--select @TableName = 'KPI_InternalTypes', @SchemaName = 'dbo', @ShowDetailes = 1

-----------------------------------------------------------------------

-- 1. table Constraints

SELECT 'table Constraints' as msg, OBJECT_NAME(OBJECT_ID) AS NameofConstraint,

SCHEMA_NAME(schema_id) AS SchemaName,

OBJECT_NAME(parent_object_id) AS TableName,

type_desc AS ConstraintType

into #Temp 

FROM sys.objects

WHERE type_desc LIKE '%CONSTRAINT'

and OBJECT_NAME(parent_object_id) = @TableName and SCHEMA_NAME(schema_id) = @SchemaName

-- 2. foreign keys Constraints from other tables

select  'foreign keys constraints' as msg,

object_name(f.constraint_object_id) as NameofConstraint,

SCHEMA_NAME(o.schema_id) AS SchemaName,

object_name(f.parent_object_id) AS TableName,

type_desc AS ConstraintType

into #Temp2

from sys.foreign_key_columns f

inner join sys.objects o on o.object_id = f.constraint_object_id  and SCHEMA_NAME(o.schema_id) = @SchemaName

where object_name(f.referenced_object_id) = @TableName

if @ShowDetailes = 1

begin

select * from #Temp

union all

select * from #Temp2

end

-- 1. table Constraints

select msg, 'ALTER TABLE [' + SchemaName + '].[' + TableName + '] DROP CONSTRAINT [' + NameofConstraint + ']' as Statement_

from #Temp2

UNION all

-- 2. foreign keys Constraints from other tables

select msg, 'ALTER TABLE [' + SchemaName + '].[' + TableName + '] DROP CONSTRAINT [' + NameofConstraint + ']' as Statement_

from #Temp

UNION all

-- 3. drop statement for the table

select 'table', 'DROP TABLE  [' + @SchemaName + '].[' + @TableName + ']' as Statement_

drop table #Temp

drop table #Temp2

go