Refactor - View Dependencies

When I use Refactor --> View Dependecies in Version 6 for a specific object I have a message returned saying "283 objects scanned; found 1 object with references"

If I use the View Dependecies in SSMS for the same object it returns about 20.

Thoughts?

That doesn't sound correct. How do you invoke SQL Assistant's dependencies search method? Which dependencies search options do you have select? Especially do you use "Query system dependencies view" or "Perform SQL code scanning and parsing" option?

I invoke SQL Assistant's dependencies search method by the following steps:

- I highlight a table name in SSMS
- I leave all default options checked
- click next and the program begins to scan
- Message says: Dependencies scan status: 7 objects scanned; found 1 object with references

I also performed the same steps above with "Perform SQL code scanning and parsing" and get the following message: Dependencies scan status: 253 objects scanned; found 1 object with references

That's weird. Does your database use case-sensitive string comparison? If yes, do you enter the name exactly as it is known top the database (the correct casing)?
To check, try SELECT name FROM sysobjects WHERE name = 'name to check here'

No it does not?

Other thoughts?

What does the following query return?

	Code:
	SELECT DISTINCT u.name, o.name FROM dbo.syscomments c JOIN dbo.sysobjects o ON o.id = c.id JOIN dbo.sysusers u ON u.uid = o.uid WHERE c.text LIKE '%name here%'

Please replace "name here" with the name of the object you search dependencies for.

By the way, when you do dependencies search, do you have database context set to the right database?

the statement above returns 108 records in SSMS when I input the table name.

The refactoring returns "Dependencies scan status: 109 objects found: found 0 objects with references"

It returns 1 more than SSMS because it counts the editor. I tested this with a few other table names and it matches on all of them.

Why does the program find the objects but say "found 0 objects with references?"

That's weird. What is the exact name of the object you are checking? Does it work for other names?

I tested this for numerous objects and receive the same results.

The exact name of the initial object I was testing is: CDS_ACCOUNT

Is there anything else i can provide to help you?

I got a suggestion from development team. You may have some SQL Assistant DDL queries or tool settings misconfigured.

To verify, enter CDS_ACCOUNT name into the editor, set the right database context, right-click that name then SQL Assistant -. Show Object DDL... menu
Do you get correct DDL script? how does it look?

looks good.

I took the DDL and ran it and it craeted the table with no error.

I have already unstalled and reinstalled SQL Assistant 6.0

I am using all default settings.

I just tried processing the refactor on a 2008 server and it appears to be working fine. Are there settings that need to be changed on a 2000 server?

Would you please try the same "Show Object DDL" for a stored procedure or function? We actually need to see what get's extracted as the DDL, there might be in the extracted code that is actually throwing the code parser off the track. So, please post the extracted DDL. Any procedure or function with several lines of code should be ok for the test.

I tried numerous functions and procedures.

I get the following for all:

/*
Cannot find source code for 'dbo.DJC_DiamondsUpdate'.
Check the "DDL Code (MSSQL)" query in SQL Assistant Options.
*/

But DDL for Tables return results.

I was looking in the options and not sure what to check for the above comment. "Check the "DDL Code (MSSQL)" query in SQL Assistant Options."

Well, this explains why you are having problems with dependencies and refactoring. As long as SQL Assistant is unabnle to retrieve code from syscomments table in SQL 2000, it cannot correctly parse dependecies and od the refactoring work.

The query can be found here Options dialog -> DB Options tab -> DB Queries option group (expand on the left) -> scoll down and select DDL Code (MSSQL) query -> check the text of the query, it should look like the following:

Code:

IF (@@version LIKE 'Microsoft SQL Server 2005%'
OR @@version LIKE 'Microsoft SQL Server 2008%')
IF EXISTS (SELECT * FROM [$DB_NAME$].sys.synonyms WHERE object_id = :OBJECT_ID)
   SELECT 'CREATE SYNONYM [' + s.name + '].[' + o.name + '] FOR ' + o.base_object_name
   FROM [$DB_NAME$].sys.synonyms o
   JOIN [$DB_NAME$].sys.schemas s ON s.schema_id = o.schema_id
   WHERE o.object_id = :OBJECT_ID
ELSE
   SELECT text
   FROM [$DB_NAME$].dbo.syscomments
   WHERE id = :OBJECT_ID
   ORDER BY colid
ELSE
SELECT text
FROM [$DB_NAME$].dbo.syscomments
WHERE id = :OBJECT_ID
ORDER BY colid

Make sure you have permissions to SELECT from syscomments table in the target database

OK. We made some progress.

I replaced your code with what was in there. I copied it to the bottom. Is that a bug that the defualt code is different than your code?

Question: For one of my objects it will return 54 results. if I run:
SELECT DISTINCT u.name, o.name
FROM dbo.syscomments c
JOIN dbo.sysobjects o
ON o.id = c.id
JOIN dbo.sysusers u
ON u.uid = o.uid
WHERE c.text LIKE ('%cds_player%') ORDER BY o.name

i get 74 results. I notice the results it doesn't return are when I reference the table in another database CDS_PLAYER in another database. Is that correct.

IF (@@version LIKE 'Microsoft SQL Server 2005%'
OR @@version LIKE 'Microsoft SQL Server 2008%')
AND EXISTS (SELECT * FROM [$DB_NAME$].sys.synonyms WHERE object_id = :OBJECT_ID)
SELECT 'CREATE SYNONYM [' + s.name + '].[' + o.name + '] FOR ' + o.base_object_name
FROM [$DB_NAME$].sys.synonyms o
JOIN [$DB_NAME$].sys.schemas s ON s.schema_id = o.schema_id
WHERE o.object_id = :OBJECT_ID
ELSE
SELECT text
FROM [$DB_NAME$].dbo.syscomments
WHERE id = :OBJECT_ID
ORDER BY colid