2005 readiness

,

A script to test your database for 2005 readiness.

these features are as described by the SQL 2000 BPA, however this script will run on SL 6.5,7,2000

Please note that this is not a guarantee of readiness, however it is an indication that there are issues you may have to resolve to run your databases in 2005

--SQL server 2005 readiness

--This rule checks that user tables (xtype='U') named dbo.sysproperties and 
--dbo.sysfulltextnotify do not exist in specified databases.
declare @dbname nvarchar(50)
declare @strsql nvarchar(4000)

declare curs1 cursor for select name from master.dbo.sysdatabases where name not in ('master','model','msdb','tempdb')
open curs1
fetch next from curs1 into @dbname
while @@fetch_status=0
begin
	print 'checking for sysfulltextnotify and sysproperties in '+@dbname
	set @strsql='
	if (select count(*) from '+@dbname+'.dbo.sysobjects where xtype=''u'' and name in(''sysproperties'',''sysfulltextnotify''))>0
	begin
		print ''NONCOMPLIANCE database '+@dbname+' contains dbo.sysproperties or dbo.sysfulltextnotify - they should be renamed or removed prior to upgrading''
	end'
	--print @strsql
	exec sp_executesql @strsql
fetch next from curs1 into @dbname
end
close curs1
deallocate curs1


--This rule checks for the use of DBCC commands that have been deprecated from SQL Server. 

--A future release of SQL Server will not support these DBCC commands:

--DBCC DBREPAIR 
--DBCC NEWALLOC 
--DBCC PINTABLE 
--DBCC ROWLOCK 
--DBCC TEXTALL 
--DBCC TEXTALLOC 
--DBCC UNPINTABLE 


declare curs1 cursor for select name from master.dbo.sysdatabases where name not in ('master','model','msdb','tempdb')
open curs1
fetch next from curs1 into @dbname
while @@fetch_status=0
begin
	print 'checking for unsupported DBCC in '+@dbname
set @strsql='
if (select count(*) from '+@dbname+'.dbo.sysobjects sysobjects,'+@dbname+'.dbo.syscomments syscomments 
	where sysobjects.id=syscomments.id and (syscomments.text like ''%DBREPAIR%'' or 
	syscomments.text like ''%NEWALLOC%'' or syscomments.text like ''%PINTABLE%'' or
	syscomments.text like ''%ROWLOCK%'' or syscomments.text like ''%TEXTALL%'' or
	syscomments.text like ''%TEXTALLOC%'' or syscomments.text like ''%UNPINTABLE%'') )>0
	begin
		print ''NONCOMPLIANCE database '+@dbname+' contains DBCC commands that have been deprecated from SQL Server.''
		select ''INVALID DBCC'',sysobjects.name from '+@dbname+'.dbo.sysobjects sysobjects,'+@dbname+'.dbo.syscomments syscomments 
	where sysobjects.id=syscomments.id and (syscomments.text like ''%DBREPAIR%'' or 
	syscomments.text like ''%NEWALLOC%'' or syscomments.text like ''%PINTABLE%'' or
	syscomments.text like ''%ROWLOCK%'' or syscomments.text like ''%TEXTALL%'' or
	syscomments.text like ''%TEXTALLOC%'' or syscomments.text like ''%UNPINTABLE%'')
	end'
	--print @strsql
	exec sp_executesql @strsql
fetch next from curs1 into @dbname
end
close curs1
deallocate curs1



--This rule checks for the use of sp_configure commands that have been deprecated from SQL Server. 
--A future release of SQL Server will not support these sp_configure commands:
--sp_configure 'allow updates' 
--sp_configure 'open objects' 
--sp_configure 'set working set size' 

declare curs1 cursor for select name from master.dbo.sysdatabases where name not in ('master','model','msdb','tempdb')
open curs1
fetch next from curs1 into @dbname
while @@fetch_status=0
begin
	print 'checking for unsupported sp_configure in '+@dbname
set @strsql='
if (select count(*) from '+@dbname+'.dbo.sysobjects sysobjects,'+@dbname+'.dbo.syscomments syscomments 
	where sysobjects.id=syscomments.id and syscomments.text like ''%sp_configure%'' and (syscomments.text like ''%set working set size%'' or 
	syscomments.text like ''%open objects%'' or syscomments.text like ''%allow updates%'') )>0
	begin
		print ''NONCOMPLIANCE database '+@dbname+' contains SP_Configure commands that have been deprecated from SQL Server.''
		select ''INVALID SP_CONFIGURE'',sysobjects.name from '+@dbname+'.dbo.sysobjects sysobjects,'+@dbname+'.dbo.syscomments syscomments 
	where sysobjects.id=syscomments.id and syscomments.text like ''%sp_configure%'' and (syscomments.text like ''%set working set size%'' or 
	syscomments.text like ''%open objects%'' or syscomments.text like ''%allow updates%'')
	end'
	--print @strsql
	exec sp_executesql @strsql
fetch next from curs1 into @dbname
end
close curs1
deallocate curs1



--This rule checks for the use of stored procedures that are obsolete in SQL Server 2005. 

--A future release of SQL Server will not support these stored procedures:

--sp_articlesynctranprocs 
--sp_diskdefault 
--sp_helplog 
--sp_helpsql 
--sp_scriptdynamicupdproc 

declare curs1 cursor for select name from master.dbo.sysdatabases where name not in ('master','model','msdb','tempdb')
open curs1
fetch next from curs1 into @dbname
while @@fetch_status=0
begin
	print 'checking for unsupported stored procedures in '+@dbname
set @strsql='
if (select count(*) from '+@dbname+'.dbo.sysobjects sysobjects,'+@dbname+'.dbo.syscomments syscomments 
	where sysobjects.id=syscomments.id and (syscomments.text like ''%sp_articlesynctranprocs %'' or 
	syscomments.text like ''%sp_diskdefault%'' or syscomments.text like ''%sp_helplog%''or syscomments.text 
	like ''%sp_helpsql%''or syscomments.text like ''%sp_scriptdynamicupdproc%'') )>0
	begin
		print ''NONCOMPLIANCE database '+@dbname+' contains stored procedures that have been deprecated from SQL Server.''
		select ''UNSUPPORTED STORED PROCEDURES'',sysobjects.name from '+@dbname+'.dbo.sysobjects sysobjects,'+@dbname+'.dbo.syscomments syscomments 
	where sysobjects.id=syscomments.id and (syscomments.text like ''%sp_articlesynctranprocs %'' or 
	syscomments.text like ''%sp_diskdefault%'' or syscomments.text like ''%sp_helplog%''or syscomments.text 
	like ''%sp_helpsql%''or syscomments.text like ''%sp_scriptdynamicupdproc%'')
	end'
	--print @strsql
	exec sp_executesql @strsql
fetch next from curs1 into @dbname
end
close curs1
deallocate curs1


--This rule checks for the use of system tables that have been deprecated from SQL Server. 

--A future release of SQL Server will not support these system tables:

--syslocks 
--sysproperties 
--sysxlogins 

declare curs1 cursor for select name from master.dbo.sysdatabases where name not in ('master','model','msdb','tempdb')
open curs1
fetch next from curs1 into @dbname
while @@fetch_status=0
begin
	print 'checking for unsupported system tables in '+@dbname
set @strsql='
if (select count(*) from '+@dbname+'.dbo.sysobjects sysobjects,'+@dbname+'.dbo.syscomments syscomments 
	where sysobjects.id=syscomments.id and (syscomments.text like ''%syslocks%'' or 
	syscomments.text like ''%sysproperties%'' or syscomments.text like ''%sysxlogins%'') )>0
	begin
		print ''NONCOMPLIANCE database '+@dbname+' contains references to system tables that have been deprecated from SQL Server.''
		select ''INVALID USE OF SYSTEM TABLES'',sysobjects.name from '+@dbname+'.dbo.sysobjects sysobjects,'+@dbname+'.dbo.syscomments syscomments 
	where sysobjects.id=syscomments.id and (syscomments.text like ''%syslocks%'' or 
	syscomments.text like ''%sysproperties%'' or syscomments.text like ''%sysxlogins%'')
	end'
	--print @strsql
	exec sp_executesql @strsql
fetch next from curs1 into @dbname
end
close curs1
deallocate curs1


--This rule checks stored procedures, functions, views and triggers for use of ORDER BY clause specifying constants as sort columns. Integers are not reported. Support for this syntax has been deprecated and will not be supported in SQL Server 2005 under 90 compatibility level. 

--As an example, the following syntax is not recommended:
--SELECT au_id FROM dbo.authors ORDER BY 'a'
--SELECT au_id FROM dbo.authors ORDER BY 1
--select * from sysusers order by NULL


declare curs1 cursor for select name from master.dbo.sysdatabases where name not in ('master','model','msdb','tempdb')
open curs1
fetch next from curs1 into @dbname
while @@fetch_status=0
begin
	print 'checking for unsupported ORDER BY in '+@dbname
set @strsql='
if (select count(*) from '+@dbname+'.dbo.sysobjects sysobjects,'+@dbname+'.dbo.syscomments syscomments 
	where sysobjects.id=syscomments.id and (syscomments.text like ''%ORDER BY [''''01-9]%'') )>0
	begin
		print ''NONCOMPLIANCE database '+@dbname+' contains ORDER BY clauses that have been deprecated from SQL Server.''
		select ''INVALID ORDER BY'',sysobjects.name,syscomments.text from '+@dbname+'.dbo.sysobjects sysobjects,'+@dbname+'.dbo.syscomments syscomments 
	where sysobjects.id=syscomments.id and (syscomments.text like ''%ORDER BY [''''01-9]%'')
	end'
	--print @strsql
	exec sp_executesql @strsql
fetch next from curs1 into @dbname
end
close curs1
deallocate curs1

--This rule checks that a schema called 'sys' has not been defined.

--SQL Server 2005 has rearchitected its system catalog to be better organized and to expose metadata 
--information in a consistent way, without forcing users to see and understand implementation details 
--of the catalog itself. As part of this effort, all system objects have been moved to a newly reserved 
--'sys' schema. As such, existing databases with 'sys' schema defined in them will not be able to upgrade 
--to SQL Server 2005 without first renaming such schema.

declare curs1 cursor for select name from master.dbo.sysdatabases where name not in ('master','model','msdb','tempdb')
open curs1
fetch next from curs1 into @dbname
while @@fetch_status=0
begin
	print 'checking for unsupported sys schema in '+@dbname
set @strsql='
if (select count(*) from '+@dbname+'.dbo.sysobjects sysobjects,'+@dbname+'.dbo.syscomments syscomments 
	where sysobjects.id=syscomments.id and (syscomments.text like ''%sys.'') )>0
	begin
		print ''NONCOMPLIANCE database '+@dbname+' contains sys schema (references) that will not upgrade to SQL Server 2005.''
		select ''INVALID SCHEMA'',sysobjects.name,syscomments.text from '+@dbname+'.dbo.sysobjects sysobjects,'+@dbname+'.dbo.syscomments syscomments 
	where sysobjects.id=syscomments.id and (syscomments.text like ''%sys.%'')
	end
if (select count(*) from '+@dbname+'.dbo.sysusers sysusers
	where sysusers.name=''sys'' )>0
	begin
		print ''NONCOMPLIANCE database '+@dbname+' contains sys schema (username) that will not upgrade to SQL Server 2005.''
	end'
	--print @strsql
	exec sp_executesql @strsql
fetch next from curs1 into @dbname
end
close curs1
deallocate curs1

/*
This rule checks stored procedures, triggers, views and functions for use of table hints without the WITH keyword.
It is recommended that hints be specified using the WITH keyword.
SQL Server 2005 will not support the use of more than one hints in a T-SQL statement unless the WITH keyword is specified.
As an example, the following syntax is not supported in SQL Server 2005:
     SELECT au_id FROM dbo.authors (UPDLOCK, PAGLOCK)
The recommended approach is to use:
     SELECT au_id FROM dbo.authors WITH (UPDLOCK, PAGLOCK)
Queries specifying a single hint will work in SQL Server 2005, though it is still recommended to use the WITH keyword.

Table  FASTFIRSTROW Has the same effect as specifying the FAST 1 query hint. No such optimization. 
Table  INDEX =  Instructs SQL Server to use the specified indexes for a table. Chosen by SQL Server. 
Table  HOLDLOCK  
|  SERIALIZABLE     
|  REPEATABLEREAD  
|  READCOMMITTED  
|  READUNCOMMITTED
|  NOLOCK Specifies the isolation level for a table. Defaults to a transaction isolation level. 
Table  ROWLOCK
|  PAGLOCK
|  TABLOCK
|  TABLOCKX
|  NOLOCK Specifies locking granularity for a table. Chosen by SQL Server. 
Table  READPAST Skips locked rows altogether. Wait for locked rows. 
Table  UPDLOCK Takes update locks instead of shared locks. Cannot be used with NOLOCK or XLOCK. Take shared locks. 
Table XLOCK 

*/

declare curs1 cursor for select name from master.dbo.sysdatabases where name not in ('master','model','msdb','tempdb')
open curs1
fetch next from curs1 into @dbname
while @@fetch_status=0
begin
	print 'checking for unsupported TABLE HINT in '+@dbname
set @strsql='
if (select count(*) from '+@dbname+'.dbo.sysobjects sysobjects,'+@dbname+'.dbo.syscomments syscomments 
	where sysobjects.id=syscomments.id and (syscomments.text not like ''%WITH%'') and 
((syscomments.text like ''%FASTFIRSTROW%'') or
(syscomments.text like ''%INDEX%'') or
(syscomments.text like ''%HOLDLOCK%'') or
(syscomments.text like ''%SERIALIZABLE%'') or
(syscomments.text like ''%REPEATABLEREAD%'') or
(syscomments.text like ''%READCOMMITTED%'') or
(syscomments.text like ''%READUNCOMMITTED%'') or
(syscomments.text like ''%NOLOCK%'') or
(syscomments.text like ''%PAGLOCK%'') or
(syscomments.text like ''%TABLOCK%'') or
(syscomments.text like ''%TABLOCKX%'') or
(syscomments.text like ''%READPAST%'') or
(syscomments.text like ''%UPDLOCK%'') or
(syscomments.text like ''%XLOCK%''))
)>0
	begin
		print ''POTENTIAL NONCOMPLIANCE database '+@dbname+' contains Invalid table hints in SQL Server.''
		select ''INVALID TABLE HINT'',sysobjects.name,syscomments.text from '+@dbname+'.dbo.sysobjects sysobjects,'+@dbname+'.dbo.syscomments syscomments 
	where sysobjects.id=syscomments.id and (syscomments.text not like ''%WITH%'') and 
((syscomments.text like ''%FASTFIRSTROW%'') or
(syscomments.text like ''%INDEX%'') or
(syscomments.text like ''%HOLDLOCK%'') or
(syscomments.text like ''%SERIALIZABLE%'') or
(syscomments.text like ''%REPEATABLEREAD%'') or
(syscomments.text like ''%READCOMMITTED%'') or
(syscomments.text like ''%READUNCOMMITTED%'') or
(syscomments.text like ''%NOLOCK%'') or
(syscomments.text like ''%PAGLOCK%'') or
(syscomments.text like ''%TABLOCK%'') or
(syscomments.text like ''%TABLOCKX%'') or
(syscomments.text like ''%READPAST%'') or
(syscomments.text like ''%UPDLOCK%'') or
(syscomments.text like ''%XLOCK%''))
	end'
	--print @strsql
	exec sp_executesql @strsql
fetch next from curs1 into @dbname
end
close curs1
deallocate curs1


--DEPRECATED FEATURES
--CREATE DATABASE with FOR LOAD
--Defaults and Rules
--Deprecated Builtin Functions
--Non-Ansi Outer Joins
--SETUSER usage
--String = Expression Aliasing
--Use of sp_dboption 


/*
This rule checks stored procedures, functions, views and triggers for existence of defaults and rules. 

These objects have been deprecated in favor of CHECK constraints and will not be supported in a future release of SQL Server.

*/


declare curs1 cursor for select name from master.dbo.sysdatabases where name not in ('master','model','msdb','tempdb')
open curs1
fetch next from curs1 into @dbname
while @@fetch_status=0
begin
	print 'checking for DEFAULTS AND RULES in '+@dbname
set @strsql='
if (select count(*) from '+@dbname+'.dbo.sysobjects sysobjects,'+@dbname+'.dbo.syscomments syscomments 
	where sysobjects.id=syscomments.id  and 
((syscomments.text like ''%CREATE RULE%'') or
(syscomments.text like ''%CREATE DEFAULT%'') or
(sysobjects.xtype=''D'') or
(sysobjects.xtype=''R''))
)>0
	begin
		print ''DEPRECATED FEATURE IN DATABASE '+@dbname+' contains Rules or Defaults.''
		select ''DEPRECATED RULE OR DEFAULT'',sysobjects.name,syscomments.text from '+@dbname+'.dbo.sysobjects sysobjects,'+@dbname+'.dbo.syscomments syscomments 
	where sysobjects.id=syscomments.id  and 
((syscomments.text like ''%CREATE RULE%'') or
(syscomments.text like ''%CREATE DEFAULT%'') or
(sysobjects.xtype=''D'') or
(sysobjects.xtype=''R''))
	end'
	--print @strsql
	exec sp_executesql @strsql
fetch next from curs1 into @dbname
end
close curs1
deallocate curs1

/*
This rule checks stored procedures, functions, views and triggers for use of builtin functions that have been deprecated from SQL Server. 

A future release of SQL Server will not support these functions:

DATABASEPROPERTY 
SUSER_ID 
SUSER_NAME 


*/

declare curs1 cursor for select name from master.dbo.sysdatabases where name not in ('master','model','msdb','tempdb')
open curs1
fetch next from curs1 into @dbname
while @@fetch_status=0
begin
	print 'checking for DEPRECATED BUILTIN FUNCTIONS in '+@dbname
set @strsql='
if (select count(*) from '+@dbname+'.dbo.sysobjects sysobjects,'+@dbname+'.dbo.syscomments syscomments 
	where sysobjects.id=syscomments.id  and 
((syscomments.text like ''%DATABASEPROPERTY%'') or
(syscomments.text like ''%SUSER_NAME%'') or
(syscomments.text like ''%SUSER_ID%''))
)>0
	begin
		print ''DEPRECATED FEATURE IN DATABASE '+@dbname+' DEPRECATED FUNCTION.''
		select ''DEPRECATED BUILT IN FUNCTION'',sysobjects.name,syscomments.text from '+@dbname+'.dbo.sysobjects sysobjects,'+@dbname+'.dbo.syscomments syscomments 
	where sysobjects.id=syscomments.id  and 
((syscomments.text like ''%DATABASEPROPERTY%'') or
(syscomments.text like ''%SUSER_NAME%'') or
(syscomments.text like ''%SUSER_ID%''))
	end'
	--print @strsql
	exec sp_executesql @strsql
fetch next from curs1 into @dbname
end
close curs1
deallocate curs1


/*
This rule checks for the use of non-ANSI outer joins. (*= and =* syntax)

Joins are specified in the FROM clause in DELETE, SELECT, and UPDATE statements. Syntax is as follows:

[ FROM { < table_source > } [ ,...n ] ] 

< table_source > ::= 
      table_name [ [ AS ] table_alias       ] [ WITH ( < table_hint > [ ,...n ] ) ] 
      | view_name [ [ AS ] table_alias ] [ WITH ( < view_hint > [ ,...n ] ) ] 
      | rowset_function [ [ AS ] table_alias ] 
      | user_defined_function [ [ AS ] table_alias ]
      | derived_table [ AS ] table_alias [ ( column_alias [ ,...n ] ) ] 
      | < joined_table >

< joined_table > ::= 
      < table_source > < join_type > < table_source > ON < search_condition > 
      | < table_source > CROSS JOIN < table_source > 
      | [ ( ] < joined_table > [ ) ] 

< join_type > ::= 
           [ INNER | { { LEFT | RIGHT | FULL } [ OUTER] } ] 
      [ < join_hint > ] 
           JOIN 

It is recommended that outer joins use the ANSI specified syntax, for example:

SELECT *
    FROM tab1 LEFT OUTER JOIN tab2 ON tab1.c3 = tab2.c3
        RIGHT OUTER JOIN tab3 LEFT OUTER JOIN tab4
            ON tab3.c1 = tab4.c1
            ON tab2.c3 = tab4.c3 

*/

declare curs1 cursor for select name from master.dbo.sysdatabases where name not in ('master','model','msdb','tempdb')
open curs1
fetch next from curs1 into @dbname
while @@fetch_status=0
begin
	print 'checking for DEPRECATED ANSI JOINS in '+@dbname
set @strsql='
if (select count(*) from '+@dbname+'.dbo.sysobjects sysobjects,'+@dbname+'.dbo.syscomments syscomments 
	where sysobjects.id=syscomments.id  and 
((syscomments.text like ''%*=%'') or
(syscomments.text like ''%=*%''))
)>0
	begin
		print ''DEPRECATED FEATURE IN DATABASE '+@dbname+' DEPRECATED ANSI JOINS.''
		select ''DEPRECATED DEPRECATED ANSI JOINS'',sysobjects.name,syscomments.text from '+@dbname+'.dbo.sysobjects sysobjects,'+@dbname+'.dbo.syscomments syscomments 
	where sysobjects.id=syscomments.id  and 
((syscomments.text like ''%*=%'') or
(syscomments.text like ''%=*%''))
	end'
	--print @strsql
	exec sp_executesql @strsql
fetch next from curs1 into @dbname
end
close curs1
deallocate curs1


/*

This rule checks stored procedures, functions, views and triggers for use of SETUSER statement. In general, SETUSER is not recommended. Support for this statement has been deprecated and will not be supported in a future release of SQL Server. 

*/



declare curs1 cursor for select name from master.dbo.sysdatabases where name not in ('master','model','msdb','tempdb')
open curs1
fetch next from curs1 into @dbname
while @@fetch_status=0
begin
	print 'checking for DEPRECATED SETUSER in '+@dbname
set @strsql='
if (select count(*) from '+@dbname+'.dbo.sysobjects sysobjects,'+@dbname+'.dbo.syscomments syscomments 
	where sysobjects.id=syscomments.id  and 
((syscomments.text like ''%SETUSER%''))
)>0
	begin
		print ''DEPRECATED FEATURE IN DATABASE '+@dbname+' DEPRECATED SETUSER STATEMENT.''
		select ''DEPRECATED SETUSER STATEMENT'',sysobjects.name,syscomments.text from '+@dbname+'.dbo.sysobjects sysobjects,'+@dbname+'.dbo.syscomments syscomments 
	where sysobjects.id=syscomments.id  and 
((syscomments.text like ''%SETUSER%''))
	end'
	--print @strsql
	exec sp_executesql @strsql
fetch next from curs1 into @dbname
end
close curs1
deallocate curs1

/*
This rule checks stored procedures, functions, views and triggers for use of sp_dboption procedure. 

This stored procedure has been deprecated in favor of ALTER DATABASE statement and will not be supported in a future release of SQL Server.
*/

declare curs1 cursor for select name from master.dbo.sysdatabases where name not in ('master','model','msdb','tempdb')
open curs1
fetch next from curs1 into @dbname
while @@fetch_status=0
begin
	print 'checking for DEPRECATED sp_dboption in '+@dbname
set @strsql='
if (select count(*) from '+@dbname+'.dbo.sysobjects sysobjects,'+@dbname+'.dbo.syscomments syscomments 
	where sysobjects.id=syscomments.id  and 
((syscomments.text like ''%sp_dboption%''))
)>0
	begin
		print ''DEPRECATED FEATURE IN DATABASE '+@dbname+' DEPRECATED sp_dboption.''
		select ''DEPRECATED sp_dboption STATEMENT'',sysobjects.name,syscomments.text from '+@dbname+'.dbo.sysobjects sysobjects,'+@dbname+'.dbo.syscomments syscomments 
	where sysobjects.id=syscomments.id  and 
((syscomments.text like ''%sp_dboption%''))
	end'
	--print @strsql
	exec sp_executesql @strsql
fetch next from curs1 into @dbname
end
close curs1
deallocate curs1

/*
This rule checks stored procedures, functions, views and triggers for use of column aliasing where the name of the expression uses a string value. It is recommended to use quoted identifiers instead. String aliases will not be supported in a future release of SQL Server. 

As an example, the following syntax is not recommended:

     SELECT 'alias_for_col'=au_id+au_id FROM dbo.authors

Recommended alternatives are:

     SELECT au_id+au_id as "alias_for_col" FROM dbo.authors

     SELECT au_id+au_id as alias_for_col FROM dbo.authors

     SELECT au_id+au_id as [alias_for_col] FROM dbo.authors

*/

declare curs1 cursor for select name from master.dbo.sysdatabases where name not in ('master','model','msdb','tempdb')
open curs1
fetch next from curs1 into @dbname
while @@fetch_status=0
begin
	print 'checking for DEPRECATED column aliasing in '+@dbname
set @strsql='
if (select count(*) from '+@dbname+'.dbo.sysobjects sysobjects,'+@dbname+'.dbo.syscomments syscomments 
	where sysobjects.id=syscomments.id  and 
((syscomments.text like ''%select%'') and (syscomments.text like ''%[''''=]%''))
)>0
	begin
		print ''POTENTIAL DEPRECATED FEATURE IN DATABASE '+@dbname+' DEPRECATED column aliasing.''
		select ''DEPRECATED column aliasing'',sysobjects.name,syscomments.text from '+@dbname+'.dbo.sysobjects sysobjects,'+@dbname+'.dbo.syscomments syscomments 
	where sysobjects.id=syscomments.id  and 
((syscomments.text like ''%select%'') and (syscomments.text like ''%[''''=]%''))
	end'
	--print @strsql
	exec sp_executesql @strsql
fetch next from curs1 into @dbname
end
close curs1
deallocate curs1

Rate

Share

Share

Rate