Work Life: MS Sql SMO Script StringCollection, Understanding why result is a StringCollection

Understanding StringCollection with MS Sql SMO Script took me some time to understand better. For some reason it did not occur to me why there are multiple lines. I initially did not care, so I did some workarounds.

Initially, I didn't care because I was using it to compare the same objects across multiple servers so as long as all environments were consistent it did not matter, so I just joined it all together or removed the first two values.

I was then put in charge of managing the source control. The script that was generated for database project did not include the ANSI_NULL or QUOTED_IDENTIFIER options. There are over 10k objects so I cannot possibly manually remove them.

I tried to find a ScriptionOption to remove them but all the answerers bypasses the question by saying that it is required (or at least highly recommended). Most questioners problems are that they are missing the GO command.

Although there are plenty of questions to remove, I could not find any answers that removed them. I have gone through many versions of the same unsatisfying answers. The best is just to add it to the code to ignore those lines or add GO after each record.

Summary

Each record in the StringCollection is a SQL command. Although satisfying to know the answer, a little disappointed in myself that it took this look to figure it out.

I still don't like that none of the Microsoft solutions are ever consistent though. SSMS includes not only ANSI_NULLS and QUOTED_IDENTIFER but also the USE [DatabaseName]. While the SMO Script method includes the ANSI_NULL and QUOTED_IDENTIFIER but does not include the USE. And then the database import into Visual Studio or Azure Data Studio do not include ANSI_NULLS and QUOTED_IDENTIFER but includes a GO at the end.

Run as Script

Join all the rows with a GO command between each record:

myStringCollection.Join("\r\nGO\r\n");

For Source Control

Because database project does not include the ANSI_NULL and QUOTED_IDENTIFIER, I remove all those records. Requesting multiple objects is more troublesome, so I just request one object at a time. The reason it is more troublesome is because there is no strong correlation between the script and the object it is for without assuming it is in the same order as the request or parsing the script. Because this is for source control, it is not worth the risk of pulling the incorrect script.