Tuesday, November 27, 2018

CASE / CONCATENATE a full name from multiple columns

Today a customer of mine was trying to query a user's full name out as one column, rather than as three separate columns, FirstName, MiddleName and LastName.  He was having problems with the users who did not have a MiddleName, because the NULLs were causing his concatention to come back empty.  This happens because by default, any string concatenation (+) that includes a NULL value will also produce a NULL result.  This post will show you two ways to work around that, using the AdventureWorks2012.Person.Person table.

Option #1 - CASE 
First I pull out the three columns separately, followed with my customer's statement and finishing with my CASE on the MiddleName.  The end result for records with no MiddleName is derived from First and Last name, rather than NULL:

    SELECT
       FirstName,MiddleName,LastName,
       FirstName + ' ' + MiddleName +' '+ LastName [NoGood],
    CASE 
       WHEN MiddleName IS NULL THEN FirstName + ' ' + LastName
       WHEN MiddleName IS NOT NULL THEN FirstName + ' ' + MiddleName + ' ' + 
        LastName END [FullName]
    FROM
        Person.Person;


Output:


Option #2 - CONCAT_NULL_YIELDS_NULL 
Even easier, you can use CONCAT_NULL_YIELDS_NULL to tell the server to treat the NULLs like empty strings instead of NULLs.  Run exactly the same query as above, but prefaced like this:
    
     SET CONCAT_NULL_YIELDS_NULL OFF; 
   SELECT
       FirstName,MiddleName,LastName,
       ........
       ....

Output:

Pretty snazzy.  BUT -- they say the CONCAT_NULL_YIELDS_NULL will always be ON in a future release, so this 2nd option is not a long-term solution.  See here for more details:



Can I change a normal SQL Server column to a computed column?

Nope.  Per BOL, "To change an existing column to a computed column you must drop and re-create the computed column."   Though because the computed column isn't there yet, I am fairly sure they meant  '...drop and re-create the existing column'.  See here:  Not going to happen

So what do you do?  Well, you could create a new table with computed columns and copy the data into it, OR you could rename your existing columns and create new ones with the intended computations.  I like the 2nd option a little better because I don't have to move a bunch of data around AND because I can see my old and computed columns side by side. Then, when I'm sure the computed columns are what I need -- I drop the old columns.  In this example I am adding 3 new computed columns to my CUSTOMER table.  The computations are based on other columns in the CUSTOMER table, but you see what I mean.  We rename the old column and define the new one w/the desired computation:


    -- rename old column
    EXEC sp_rename 'CUSTOMER.TSLONG', 'TSLONGold', 'COLUMN';
    -- add new column w/computation
    ALTER TABLE CUSTOMER ADD TSLONG AS (CONVERT(BIGINT,TS,0));

    -- rename old column
    EXEC sp_rename 'CUSTOMER.LOOKUPID', 'LOOKUPIDold', 'COLUMN';
    -- add new column w/computation
    ALTER TABLE CUSTOMER ADD LOOKUPID AS (CASE
      LEN(CUSTOMID) WHEN (0) THEN '8-'+CONVERT(NVARCHAR(20),SEQUENCEID,0) ELSE CUSTOMID END);

    -- rename old column
    EXEC sp_rename 'CUSTOMER.GENDER', 'GENDERold', 'COLUMN';
    -- add new column w/computation
    ALTER TABLE CUSTOMER ADD GENDER AS (CASE GENDERCODE
           WHEN (0) THEN 'Unknown'
           WHEN (1) THEN 'Female' END
           WHEN (2) THEN 'Male'
    );

Once you're sure everything is good, you'll use this to DROP the old columns:

       -- drop old columns
       ALTER TABLE CUSTOMER
       DROP COLUMN TSLONGold;
       ALTER TABLE CUSTOMER
       DROP COLUMN LOOKUPIDold;
       ALTER TABLE CUSTOMER
       DROP COLUMN GENDERold;

Or, you could even use this:

       ALTER TABLE CUSTOMER
       DROP COLUMN TSLONGold,LOOKUPIDold,GENDERold;

Did you know you drop more than one table column at a time?  How cool is that?!  Better be safe.


Hope you found this helpful.


Saturday, November 17, 2018

List all indexes for all tables in a SQL Server database

Just a quick post to show how to list all indexes for all tables in your SQL Server database.  In this example I am referencing AdventureWorks2012.

       USE AdventureWorks2012;

       SELECT
              DB_NAME() [Database],
              sc.name [Schema],
              o.name [Table],
              i.name [Index],
              i.type_desc [Index Type]
       FROM
              sys.indexes i INNER JOIN sys.objects o
                ON i.object_id = o.object_id INNER JOIN sys.schemas sc
                     ON o.schema_id = sc.schema_id
       WHERE
              i.name IS NOT NULL
              AND o.type = 'U'
       ORDER BY
              o.name, i.type


Sample output:





Thursday, November 15, 2018

Which foreign keys reference this table?

BIG task yesterday... ran into some data corruption that fortunately could be repaired by dropping/recreating the table's primary key, but -- that table had 469 foreign keys!  Try to drop the PK on any table with FK references, and you will fail.  For example, here's the error when I try to drop the PK on Person.Address in the AdventureWorks db:

      Msg 3725, Level 16, State 0, Line 18
      The constraint 'PK_Address_AddressID' is being referenced by table      
         'SalesOrderHeader', foreign key constraint 'FK_SalesOrderHeader_Address_ShipToAddressID'.
      Msg 3727, Level 16, State 0, Line 18
      Could not drop constraint. See previous errors.


Ok. So I need to drop/recreate FK_SalesOrderHeader_Address_ShippToAddressID first,  but are there more?  How do I know all of the foreign keys that reference another table's primary key?  Easy sneasy.  Just input the table and schema name from the table you are dropping the PK from.      

     EXEC sp_fkeys @pktable_name = 'Address',@pktable_owner = 'Person'


It's cut off, but you get the point.  These are the foreign keys that I must also address if I am going to drop/recreate the Person.Address primary key.  It's just a few, so easy enough to write up the code for the DROP/CREATE of the foreign keys... but what about that table with 469 foreign keys?!  NOT.  

Check out this post for a great piece that will generate the statements to both drop and recreate the referencing foreign keys for the given @PKname.  It was certainly helpful yesterday! 

And see this for more details on sp_fkeys.   sp_fkeys



Generate DROP/CREATE statements for all referencing foreign keys

This is a quick post to generate the DROP/CREATE statements for all foreign keys referencing a given primary key... like the subject says. 😉  In this example, we generate the statements for all foreign keys referencing PK_Address_AddressID on Person.Address in AdventureWorks2012, and this is the output:





See this post for a little more detail on when you might need to do this.

     CREATE TABLE #ForeignKeys (
       SchemaName VARCHAR(128),
       TableName VARCHAR(128),
       ForeignKeyName VARCHAR(128),
       DropScript VARCHAR(MAX),
       CreateScript VARCHAR(MAX)
     );

     DECLARE @PKname NVARCHAR(128) = 'PK_Address_AddressID' --<-- change this to your PK name
     DECLARE @create NVARCHAR(MAX) = '';

     -- populate the drop statement
     INSERT #ForeignKeys (SchemaName,TableName,ForeignKeyName,DropScript,CreateScript)
     SELECT schemas.[name],
       tables.[name],
       foreign_keys.[name],
       'ALTER TABLE ' + QUOTENAME(schemas.[name]) + '.' + QUOTENAME(tables.[name]) + ' DROP CONSTRAINT    
             ' + QUOTENAME(foreign_keys.[name]),
       NULL
     FROM
sys.foreign_keys INNER JOIN sys.tables
         ON foreign_keys.parent_object_id = tables.object_id INNER JOIN sys.schemas
           ON tables.schema_id = schemas.schema_id INNER JOIN sys.indexes
                ON sys.indexes.object_id = sys.foreign_keys.referenced_object_id
                AND sys.indexes.index_id = sys.foreign_keys.key_index_id
     WHERE
       indexes.name = @PKname;

     -- populate the create statement
     UPDATE #ForeignKeys
     SET CreateScript = Creation.Script
     FROM #ForeignKeys INNER JOIN
     (
     SELECT
        cs.[name] SchemaName,
        ct.[name] TableName,
        foreign_keys.[name] ForeignKeyName,
        'ALTER TABLE ' + QUOTENAME(cs.[name]) + '.' + QUOTENAME(ct.[name]) + ' ADD CONSTRAINT ' + QUOTENAME(foreign_keys.[name]) + ' FOREIGN KEY (' +
        STUFF((
              SELECT ',' + QUOTENAME(columns.[name])
              FROM sys.columns INNER JOIN sys.foreign_key_columns
                ON foreign_key_columns.parent_column_id = columns.column_id
                AND foreign_key_columns.parent_object_id = columns.object_id
              WHERE
                     foreign_key_columns.constraint_object_id = foreign_keys.object_id
              ORDER BY
                     foreign_key_columns.constraint_column_id
       FOR XML PATH(''), TYPE
                       ).value('.1', 'VARCHAR(MAX)'), 1, 1,'') + ') REFERENCES ' + QUOTENAME(schemas.[name]) + '.' + QUOTENAME(tables.[name]) + '(' +
     STUFF((
     SELECT ',' + QUOTENAME(columns.[name])
     FROM sys.columns INNER JOIN sys.foreign_key_columns fkc
       ON fkc.referenced_column_id = columns.column_id
       AND fkc.referenced_object_id = columns.object_id
     WHERE
         fkc.constraint_object_id = foreign_keys.object_id
     ORDER BY
         fkc.constraint_column_id
      FOR XML PATH(''), TYPE
       ).value('.1', 'NVARCHAR(MAX)'), 1, 1, '') + ')' Script
     FROM sys.foreign_keys INNER JOIN sys.tables
       ON foreign_keys.referenced_object_id = tables.object_id INNER JOIN sys.schemas
         ON tables.schema_id = schemas.schema_id INNER JOIN sys.tables ct
           ON foreign_keys.parent_object_id = ct.object_id INNER JOIN sys.schemas cs
             ON ct.schema_id = cs.schema_id
     WHERE
         tables.is_ms_shipped = 0
         AND ct.is_ms_shipped = 0
     ) Creation
     ON Creation.SchemaName = #ForeignKeys.SchemaName
     AND Creation.TableName = #ForeignKeys.TableName
     AND Creation.ForeignKeyName = #ForeignKeys.ForeignKeyName

     -- output your datta
     SELECT
       SchemaName,
       TableName,
       ForeignKeyName,
       DropScript,
       CreateScript
     FROM
       #ForeignKeys

     -- drop your tmp table
     DROP TABLE #ForeignKeys