Archive

Posts Tagged ‘SQL’

Updating database collation

January 10, 2014 Leave a comment

Had some fun trying to update a database collation. I didn’t have to worry about the data i just needed to the collation changed so that some cross database scripts would still function in order to carry out some testing.

 

Here is a nice little script I found that will generate scripts to change the collation on the required columns. Some of it fails if primary keys or indexes are against the columns but this will do 90%.

 

SELECT
      CAST(o.table_name + ‘ -> ‘ + c.name AS VARCHAR(60))
    , ‘ALTER TABLE ‘ + o.table_name +
        ‘ ALTER COLUMN [‘ + c.name + ‘] ‘ +
        UPPER(t.name) + ‘(‘ +
        CASE
            WHEN t.name IN (‘nchar’, ‘nvarchar’) AND c.max_length != -1 THEN CAST(c.max_length / 2 AS NVARCHAR(10))
            WHEN t.name IN (‘nchar’, ‘nvarchar’) AND c.max_length = -1 THEN ‘MAX’
            ELSE CAST(c.max_length AS NVARCHAR(10))
        END + ‘) COLLATE ‘ + @collate +
        CASE WHEN c.is_nullable = 1
            THEN ‘ NULL’
            ELSE ‘ NOT NULL’
        END
FROM sys.columns c WITH (NOWAIT)
JOIN (
    SELECT
          table_name = ‘[‘ + s.name + ‘].[‘ + o.name + ‘]’
        , o.[object_id]
    FROM sys.objects o WITH (NOWAIT)
    JOIN sys.schemas s WITH (NOWAIT) ON o.[schema_id] = s.[schema_id]
    WHERE o.[type] = ‘U’
) o ON c.[object_id] = o.[object_id]
JOIN sys.types t WITH (NOWAIT) ON c.system_type_id = t.system_type_id AND c.user_type_id = t.user_type_id
WHERE t.name IN (‘char’, ‘varchar’, ‘text’, ‘nvarchar’, ‘ntext’, ‘nchar’)
    –AND c.collation_name != @collate
ORDER BY
      o.table_name
    , c.column_id

Advertisements
Categories: work Tags:

Generate unique X and Y coordinates or grid references from a row number

August 9, 2012 1 comment

I was creating an SSRS report and using a matrix table. I needed to be able to position data within each group by. To do this i needed create a X and Y number for each item where the 2 together would be unique.

 

This is an example of what i wanted

Row number

x

y

1

0

1

2

0

2

3

0

3

4

1

1

5

1

2

6

1

3

7

2

1

8

2

2

9

2

3

 

And here is the SQL that creates it

 

SELECT  ROW_NUMBER() over (ORDER BY prj.ProjectName) as Row       
            , ((ROW_NUMBER() over (ORDER BY prj.ProjectName)) - 1) / 3  as X
            ,  ((ROW_NUMBER() over (ORDER BY prj.ProjectName)) - 1) % 3  as   Y

This took a lot longer to work out that i care to admit. So hopefully someone else will find this useful

 

If you need more columns before the split then simply change the 3 to be what ever number you need.

Categories: work Tags: ,