asked
SQL Azure Migration Issue
Hi Folks, We are planning to migrate our sql server database to SQL Azure platform but there are some issues reported by the migration wizard.The most important one is "SELECT INTO statement is not supported in this version of SQL Azure". Is there are any way to find out how many stored procedures and functions are using "Select into " clause? Regards, Hemant
Not easily, no. The quickest way that I can think of to come to an **accurate** representation of that would be to use the VSTS SQL Parser, load module definitions into that, parse them and walk the AST generated for each object, looking for that pattern. I'm not at home to test that out right now, but I'm pretty sure it's possible...
I'd take a download of SQLSearch from RedGate and let that find what it can for you. I dont know whether it would find 100% but it would do a lot.
http://www.red-gate.com/products/sql-development/sql-search/
I would say it would probably be over 100%. It's unlikely to miss things if you search for SELECT and INTO, but it would undoubtedly have false positives too. I have edited my answer to bold 'accurate' :)
You could also use a query like this one: SELECT po.name AS [ParentObjectName] ,ps.name AS [ParentObjectSchema] ,o.name AS ObjectName ,s.name AS [Schema] ,o.type AS [Type] ,o.type_desc AS [TypeDesc] ,o.create_date AS [Created] ,o.modify_date AS [Modified] ,po.type as [ParentType] ,po.type_desc AS [ParentTypeDesc] ,o.object_id AS [ObjectID] ,po.object_id AS [ParentObjectID] ,OBJECT_DEFINITION(o.object_id) AS [Definition] FROM sys.objects o INNER JOIN sys.sql_modules m ON o.object_id = m.object_id INNER JOIN sys.schemas s on o.schema_id = s.schema_id LEFT JOIN sys.objects po ON o.parent_object_id = po.object_id LEFT JOIN sys.schemas ps ON po.schema_id = ps.schema_id WHERE OBJECT_DEFINITION(o.object_id) LIKE '%SELECT%INTO%' It will go through all objects in database and search for the keyword SELECT followed by INTO. It can return some false positives also as other method could, but you can list the object asily in this way and then iterate through them and verify what you need.
