Similar names not handled

Aug 31, 2010 at 8:41 PM

I have a database I'm testing with and the script grabs the output of Get-SqlScripter.  But I have some objects very similar in name and it's only grabbing the second one.  See example below:

Get-SqlDatabase "localhost" Test | Get-SqlStoredProcedure -name usp_TestList | Get-SqlScripter

This returns the sql for usp_TestList2 not usp_TestList  If I do it with " (double quotes) does not change this behavior:

Get-SqlDatabase "localhost" Test | Get-SqlStoredProcedure -name "usp_TestList" | Get-SqlScripter

I'm having this issue with Get-SqlStoredProcedure and Get-SqlView.  This may happen with other but this is the only issue in my testing sofar.  I don't understand what is going on here.  I've also tried renaming to usp_TestListList2 same results.  This will be an issue when I move it to my "real world" instance as we have many objects that differ by just the last word.  Thank you.

Coordinator
Sep 1, 2010 at 1:40 AM

I've seen issues where the object was renamed and the name does not match the object definition. Renaming database object via SQL Server Management Studio or sp_rename will cause problems with any scripting utility.  This could be why you can't find the object. Here's a script to identify objects where the object name and object definition do not match. Run from the database in question:

SELECT TABLE_NAME AS 'OBJ',LEFT(VIEW_DEFINITION,255) AS 'DEF'
FROM INFORMATION_SCHEMA.VIEWS
WHERE VIEW_DEFINITION NOT LIKE '%' + TABLE_NAME + '%'
UNION
SELECT ROUTINE_NAME, LEFT(ROUTINE_DEFINITION,255)
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION NOT LIKE '%' + ROUTINE_NAME + '%'

 

Sep 1, 2010 at 2:45 PM

csmille19:

I tried the suggested query and I do see a one of the views I renamed view but it was only renamed in troubleshooting the duplication.

Coordinator
Sep 1, 2010 at 3:27 PM

I'm having trouble reproducing the the issue. As a test I created a two test procedures:

CREATE proc
usp_TestList
as
select 1

CREATE proc
usp_TestList
as
select 1;



CREATE proc
usp_TestList2
as
select 2;

#Both procedures are returned:

Get-SqlDatabase "$localhost" smoScript | Get-SqlStoredProcedure -name usp_TestList  | select name

Name
----
usp_TestList
usp_TestList2

#Both procedures are scripted out

Get-SqlDatabase "$localhost" smoScript | Get-SqlStoredProcedure -name usp_TestList  | get-sqlscripter
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE proc
usp_TestList
as
select 1
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE proc
usp_TestList2
as
select 2

Sep 1, 2010 at 5:44 PM

Upon further investigation of the issue I find you are correct.  The examples I was using were too long so I was not catching this.  I will have to address handling this in my script.  Seems this should only return the exact match not the wild card match.  Thank you for your help cmille19.

Coordinator
Sep 1, 2010 at 6:47 PM

I agree name should be an exact match and new parameters: include and exclude should be added which can use wildcards. This is how the get-childitem cmdlet included with PowerShell works. I've added a work item to fix the behavior in a future release.

Sep 1, 2010 at 7:32 PM

 

Thank you.