Our Blog

After copying a SQL Server database I needed to create a new schema and transfer all tables and views and stored procedures from the old schema to the newly created schema.  Using the following script made this task much easier.

DECLARE @OldSchema AS varchar(255)
DECLARE @NewSchema AS varchar(255)

SET @OldSchema = 'dbo'
SET @NewSchema = 'StackOverflow'

DECLARE @sql AS varchar(MAX)

DECLARE @Schema AS varchar(MAX)
DECLARE @Obj AS varchar(MAX)

-- First transfer Tables and Views

DECLARE CU_OBJS CURSOR FOR
    SELECT TABLE_SCHEMA, TABLE_NAME
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_SCHEMA = @OldSchema

OPEN CU_OBJS

FETCH NEXT FROM CU_OBJS
INTO @Schema, @Obj

WHILE @@FETCH_STATUS = 0
BEGIN
    SELECT @sql = 'ALTER SCHEMA [' + @NewSchema + '] TRANSFER [' + @OldSchema + '].[' + @Obj + ']'
    PRINT @sql
--  EXEC (@sql)

    FETCH NEXT FROM CU_OBJS
    INTO @Schema, @Obj
END

CLOSE CU_OBJS
DEALLOCATE CU_OBJS


-- Now transfer Stored Procedures

DECLARE CU_OBJS CURSOR FOR
    SELECT sys.schemas.name, sys.procedures.name
    FROM sys.procedures,sys.schemas
    WHERE sys.procedures.schema_id=sys.schemas.schema_id and sys.schemas.name = @OldSchema

OPEN CU_OBJS

FETCH NEXT FROM CU_OBJS
INTO @Schema, @Obj

In .NET there are 6 ways to compare strings.  Really? Why is it so difficult?

Ordinal

Performs a simple byte comparison that is independent of language. This is most appropriate when comparing strings that are generated programmatically or when comparing case-sensitive resources such as passwords.

OrdinalIgnoreCase

Treats the characters in the strings to compare as if they were converted to uppercase using the conventions of the invariant culture, and then performs a simple byte comparison that is independent of language. This is most appropriate when comparing strings that are generated programmatically or when comparing case-insensitive resources such as paths and filenames.

InvariantCulture

Compares strings in a linguistically relevant manner, but it is not suitable for display in any particular culture. Its major application is to order strings in a way that will be identical across cultures.

InvariantCultureIgnoreCase

Compares strings in a linguistically relevant manner that ignores case, but it is not suitable for display in any particular culture. Its major application is to order strings in a way that will be identical across cultures.

CurrentCulture

Can be used when strings are linguistically relevant. For example, if strings are displayed to the user, or if strings are the result of user interaction, culture-sensitive string comparison should be used to order the string data.

CurrentCultureIgnoreCase

Can be used when strings are linguistically relevant but their case is not. For example, if strings are displayed to the user but case is unimportant, culture-sensitive, case-insensitive string comparison should be used to order the string data.

Tip: You should always specify explicitly the comparer as the default value is not consistent. For instance, string.IndexOf uses the current culture whereas string.Equals uses Ordinal.

i.e.

string.Equals("", "", StringComparison.Ordinal);
new [] { "" }.Contains("", StringComparer.Ordinal);
new Dictionary(StringComparer.Ordinal);

</string,>

Refer to blog for additional samples and Rosyln analyzer to help with coding within your IDE.

About Us

Web/Mobile Solutions

Our Contacts

Cincinnati, OH 45069