DB Query Error (MSSQL + SQL Server 2000 ) with 'SCHEMA_NAME'

Version of SQL Server: 08.00.0760
Version of SQL Assistant: 6.3.171

I have error with DB QUERY Columns(MSSQL)+Keys

This query cannot be compiled by MSSQL 2000 because it does not know word 'SCHEMA_NAME'.

Code:

if @@version like 'Microsoft SQL Server 2005%'
or @@version like 'Microsoft SQL Server 2008%'
or @@version like '%SQL Server 2012%'
or @@version like '%Denali%'
select
c.name,
case when t.system_type_id = t.user_type_id then type_name(c.system_type_id)
else SCHEMA_NAME(t.[schema_id]) + '.' + type_name(c.user_type_id) + ' - ' + type_name(c.system_type_id)
end
+ case
when type_name(c.system_type_id) in ('varchar', 'char', 'binary', 'varbinary')
then '(' + case c.max_length when -1 then 'max' else convert(varchar, c.max_length) end + ')'
when type_name(t.system_type_id) in ('nvarchar', 'nchar')
then '(' + case c.max_length when -1 then 'max' else convert(varchar, c.max_length / 2) end + ')'
when type_name(t.system_type_id) in ('decimal', 'numeric')
then '(' + convert(varchar, c.[precision]) + ',' + convert(varchar, c.scale) + ')'
else ''
end,
case /* Y,N,A,D,L */
when c.is_identity = 1 then 'A'
when t.name='timestamp' then 'D'
when c.is_computed = 1 then case when c.is_nullable = 1 then 'L' else 'D' end
when c.is_nullable = 1 then 'Y' else 'N'
end
+ CASE /* P,F,U */
WHEN EXISTS(
SELECT NULL
FROM [$DB_NAME$].sys.indexes i, [$DB_NAME$].sys.index_columns k
WHERE i.[object_id] = k.[object_id] AND i.index_id = k.index_id
AND k.[object_id] = c.[object_id] AND k.column_id = c.column_id AND i.is_primary_key <> 0
) THEN 'P'
WHEN EXISTS(
SELECT NULL FROM [$DB_NAME$].sys.foreign_key_columns f
WHERE f.parent_object_id = c.[object_id] AND f.parent_column_id = c.column_id
) THEN 'F'
WHEN EXISTS(
SELECT NULL
FROM [$DB_NAME$].sys.indexes i, [$DB_NAME$].sys.index_columns k
WHERE i.[object_id] = k.[object_id] AND i.index_id = k.index_id
AND k.[object_id] = c.[object_id] AND k.column_id = c.column_id AND i.is_unique_constraint <> 0
) THEN 'U'
ELSE ' '
END
+ CASE
WHEN EXISTS(
SELECT NULL
FROM [$DB_NAME$].sys.indexes i, [$DB_NAME$].sys.index_columns k
WHERE i.[object_id] = k.[object_id] AND i.index_id = k.index_id
AND k.[object_id] = c.[object_id] AND k.column_id = c.column_id
AND i.is_primary_key = 0 AND i.is_unique_constraint = 0
) THEN 'I'
ELSE ''
END
from
[$DB_NAME$].sys.all_columns c
join [$DB_NAME$].sys.types t
on c.user_type_id = t.user_type_id
where
c.object_id = :OBJECT_ID
order by
c.column_id
else
select
c.name,
case when t.xtype = t.xusertype then type_name(c.xtype)
else type_name(c.xusertype) + ' - ' + type_name(c.xtype)
end
+ case
when type_name(c.xtype) in ('varchar', 'char', 'binary', 'varbinary')
then '(' + case c.length when -1 then 'max' else convert(varchar, c.length) end + ')'
when type_name(t.xtype) in ('nvarchar', 'nchar')
then '(' + case c.length when -1 then 'max' else convert(varchar, c.length / 2) end + ')'
when type_name(t.xtype) in ('decimal', 'numeric')
then '(' + convert(varchar, c.prec) + ',' + convert(varchar, c.scale) + ')'
else ''
end,
case /* Y,N,A */
when c.autoval is not null then 'A'
when t.name = 'timestamp' then 'D'
when c.isnullable = 1 then 'Y' else 'N'
end
+ CASE /* P,F,U */
WHEN EXISTS(
SELECT NULL
FROM [$DB_NAME$].dbo.sysindexes i, [$DB_NAME$].dbo.sysindexkeys k
WHERE i.id = k.id AND i.indid = k.indid
AND k.id = c.id AND k.colid = c.colid AND i.status & 0x800 <> 0
AND i.indid BETWEEN 1 AND 254
) THEN 'P'
WHEN EXISTS(
SELECT NULL FROM [$DB_NAME$].dbo.sysforeignkeys f WHERE f.fkeyid = c.id AND f.fkey = c.colid
) THEN 'F'
WHEN EXISTS(
SELECT NULL
FROM [$DB_NAME$].dbo.sysindexes i, [$DB_NAME$].dbo.sysindexkeys k
WHERE i.id = k.id AND i.indid = k.indid
AND k.id = c.id AND k.colid = c.colid AND i.status & 0x1000 <> 0
AND i.indid BETWEEN 1 AND 254
) THEN 'U'
ELSE ' '
END
+ CASE
WHEN EXISTS(
SELECT NULL
FROM [$DB_NAME$].dbo.sysindexes i, [$DB_NAME$].dbo.sysindexkeys k
WHERE i.id = k.id AND i.indid = k.indid
AND k.id = c.id AND k.colid = c.colid AND i.status & 0x1860 = 0
AND i.indid BETWEEN 1 AND 254
) THEN 'I'
ELSE ''
END
from
[$DB_NAME$].dbo.syscolumns c
join [$DB_NAME$].dbo.systypes t
on c.xtype = t.xtype
and c.xusertype = t.xusertype
where
c.id = :OBJECT_ID
and c.number = 0
order by
c.colid

Please replace

	Code:
	SCHEMA_NAME(t.[schema_id])

with

	Code:
	(SELECT sch.name from [$DB_NAME$].sys.schemas sch where sch.schema_id = t.[schema_id])

Yes, its work both in MSSQL 2000 and 2008. Hope that with new version of SQL Assistant there will be included correct syntax :)

It's already included in the latest builds, but in case of upgrades, the old queries are re-used as not to loose user's change if there are any

Thanks for this information! Will make uninstall next time before install.

The uninstall won't help much in that sense as it will leave any user updated files on the system.

There is a better method. Just remove files with SAS extension from subfolders in %APP_DATA%\SQL Assistant folder.

Ok! Already found .sas files and many folders of previos versions (4.6 4.7...6.2,6.3), totally - 15Mb.

Simply removing those files might result in loss of customized settings (modified DB queries, formatting, snippets, etc.). I'd rather backup those files, reload the defaults and then import (or merge if overwriting by importing would break something) any relevant changes made to those settings.