Contents tagged with sql

  • Something I learnt about SQL table type this week

    No Comments

    Q: When I try to compile the procedure below I get the error Must declare the variable '@TableType'. Can you guess why the following stored procedure does not compile?

    CREATE PROCEDURE #SystemTablesAndColumns
AS
DECLARE @TableType TABLE (id int, name sysname)

INSERT INTO @TableType
(
 id,
 name
)
SELECT id,
       name
FROM sysobjects
WHERE name LIKE 'sys%'
ORDER BY name

SELECT @TableType.id AS TableID,
       @TableType.name AS TableName,
       syscolumns.colid AS ColumnID,
       syscolumns.name AS ColumnName
FROM @TableType
  INNER JOIN
     syscolumns ON @TableType.id = syscolumns.id
ORDER BY @TableType.name,
         syscolumns.name

RETURN


    A: Because aliases are missing for the table type, as can be noted below: 
    CREATE PROCEDURE #SystemTablesAndColumns
AS
DECLARE @TableType TABLE (id int, name sysname)

INSERT INTO @TableType
(
 id,
 name
)
SELECT id,
       name
FROM sysobjects
WHERE name LIKE 'sys%'
ORDER BY name

SELECT TableType.id AS TableID,
       TableType.name AS TableName,
       syscolumns.colid AS ColumnID,
       syscolumns.name AS ColumnName
FROM @TableType AS TableType
  INNER JOIN
     syscolumns ON TableType.id = syscolumns.id
ORDER BY TableType.name,
         syscolumns.name

RETURN


    P.S.: What the temporary stored procedure above does can be acomplished in other ways. I just used it that way to show up the problem I faced this week and how I workarounded it.

    Read more...

  • Boring SQL server message errors

    No Comments

    Last week, I created some scripts to move data from an old database to a new one. Some tables consisted of many fields and the old ones were not typed, that is, all fields where char, varchar, etc. 

    INSERT INTO targetTable
(
 targetField1,
 targetField2,
 targetField3,
 ...
 targetFieldN
)
SELECT targetField1,
       targetField2,
       targetField3,
       ...
       targetFieldN
FROM sourceTable


    When I run the script, which was quite similar to the sample below, I got errors like the ones below. 

    Server: Msg 245, Level 16, State 1, Line 1
Syntax error converting the varchar value '[varchar value here]' to a column of data type int.

Server: Msg 8152, Level 16, State 9, Line 1
String or binary data would be truncated.
The statement has been terminated.


    Wouldn't these messages be nicer if they state field(s) name(s) like I rewrote below?

    Server: Msg 245, Level 16, State 1, Line 1
Syntax error converting the varchar value '[varchar value here]' to column '[column name here]' of data type int.

Server: Msg 8152, Level 16, State 9, Line 1
String or binary data would be truncated on column '[column name here]'.
The statement has been terminated.


    Because of lack of column name on this message, I had spent a lot of time figuring out which field(s) were causing these errors. I would have spared a lot of time, if messages stated which field(s) I should take care of. If you know of other boring SQL server error message(s), drop a comment or trackback and let us know about.

    Read more...

  • DA, aka Data Administrators + ERWin = MESS

    No Comments

    Last week, I found out DA team, using ERWin to synchronize models and databases, had messed up with some foreign keys on a database I architected for an application I am developing and the FKs were missing. Then the question "How to find out which FKs are missing on a database made of about 200 tables?" popped up.

    As both parent colum name and child column name have the same name on my database and PK indexes are all named like 'XPK_%', I created the script below to help me out. Take a look at it and let your comments.

    /*
DROP TABLE #constraints
*/
-- list all constraints on database
SELECT dbo.sysobjects.parent_obj AS tableid,
       'PK' AS constraintType,
        dbo.sysobjects.name AS constraintName,
        dbo.syscolumns.name AS keyColumn,
        dbo.sysindexkeys.keyno
INTO #constraints
FROM dbo.sysobjects
  INNER JOIN
     dbo.sysindexes ON dbo.sysobjects.name = dbo.sysindexes.name
  INNER JOIN
     dbo.sysindexkeys ON dbo.sysindexes.indid = dbo.sysindexkeys.indid AND
                         dbo.sysindexes.id = dbo.sysindexkeys.id
  INNER JOIN
     dbo.syscolumns ON dbo.sysindexkeys.colid = dbo.syscolumns.colid AND
                       dbo.sysobjects.parent_obj = dbo.syscolumns.id
WHERE dbo.sysobjects.xtype = 'PK'
UNION ALL
SELECT sysindexes.id AS tableid,
       'AK' AS constraintType,
       sysindexes.name AS constraintName,
       syscolumns.name AS keyColumn,
       sysindexkeys.keyno
FROM sysindexes
  INNER JOIN
     sysindexkeys ON sysindexes.id = sysindexkeys.id AND
                     sysindexes.indid = sysindexkeys.indid
  INNER JOIN
     syscolumns ON sysindexkeys.id = syscolumns.id AND
                   sysindexkeys.colid = syscolumns.colid
  INNER JOIN
     sysobjects ON syscolumns.id = sysobjects.id
WHERE (sysindexes.status & 2) <> 0 AND
      sysindexes.name NOT LIKE 'XPK_%'
UNION ALL
SELECT sysobjects.parent_obj AS tableid,
       'FK' AS constraintType,
       sysobjects.name AS constraintName,
       syscolumns.name AS keyColumn,
       sysforeignkeys.keyno
FROM sysobjects
  INNER JOIN
     sysforeignkeys ON sysobjects.id = sysforeignkeys.constid
  INNER JOIN
     syscolumns ON sysforeignkeys.fkeyid = syscolumns.id AND
                   sysforeignkeys.fkey = syscolumns.colid
  INNER JOIN
     syscolumns AS syscolumnsParent ON sysforeignkeys.rkey = syscolumnsParent.colid AND
                                       sysforeignkeys.rkeyid = syscolumnsParent.id
  INNER JOIN
     sysobjects AS sysobjectsParent ON syscolumnsParent.id = sysobjectsParent.id
WHERE sysobjects.xtype = 'F'
/*
DROP TABLE #duplicateKeyColumns
*/
-- Since both parent and child column names are the same, duplicates which are PK/AK
-- on some table might be the starting point of search
SELECT sc.name AS columnName,
       COUNT(*) AS score
INTO #duplicateKeyColumns
FROM syscolumns sc
  INNER JOIN
     sysobjects so ON sc.id = so.id
WHERE so.type = 'U' AND
      so.name NOT IN ('exclude list comes here') AND
      EXISTS(
             SELECT #constraints.keyColumn FROM #constraints
             WHERE #constraints.keyColumn = sc.name AND
                   #constraints.constraintType IN ('PK', 'AK')
            )
GROUP BY sc.name
HAVING COUNT(*) > 1
ORDER BY score DESC
/*
DROP TABLE #duplicateKeyColumnsRelatedTables
*/
SELECT sc.name AS columnName,
       so.name AS tableName,
       CASE WHEN EXISTS(
                        SELECT #constraints.keyColumn
                        FROM #constraints
                        WHERE #constraints.tableid = so.id AND
                              #constraints.keyColumn = sc.name AND
                              #constraints.constraintType IN ('PK')
                       )
            THEN '*' ELSE NULL END AS [PK],
       CASE WHEN EXISTS(
                        SELECT #constraints.keyColumn
                        FROM #constraints
                        WHERE #constraints.tableid = so.id AND
                              #constraints.keyColumn = sc.name AND
                              #constraints.constraintType IN ('AK')
                       )
            THEN '*' ELSE NULL END AS [AK],
       CASE WHEN EXISTS(
                        SELECT #constraints.keyColumn
                        FROM #constraints
                        WHERE #constraints.tableid = so.id AND
                              #constraints.keyColumn = sc.name AND
                              #constraints.constraintType IN ('FK')
                       )
            THEN '*' ELSE NULL END AS [FK]
INTO #duplicateKeyColumnsRelatedTables
FROM syscolumns sc
  INNER JOIN
     sysobjects so ON sc.id = so.id
  INNER JOIN
     #duplicateKeyColumns ON sc.name = #duplicateKeyColumns.columnName
WHERE so.type = 'U' AND
      so.name NOT IN ('exclude list comes here') AND
      (
       EXISTS(
              SELECT #constraints.keyColumn
              FROM #constraints
              WHERE #constraints.tableid = so.id AND
                    #constraints.keyColumn = sc.name AND
                    #constraints.constraintType IN ('PK', 'AK')
             ) OR
       NOT EXISTS(
                  SELECT #constraints.keyColumn
                  FROM #constraints
                  WHERE #constraints.tableid = so.id AND
                        #constraints.keyColumn = sc.name AND
                        #constraints.constraintType IN ('FK')
                 )
      )
ORDER BY #duplicateKeyColumns.score DESC,
         #duplicateKeyColumns.columnName,
         so.name
-- deletes all duplicate key columns which does not have table(s) missing any FK
DELETE #duplicateKeyColumnsRelatedTables
WHERE EXISTS(
             SELECT COUNT(*)
             FROM #duplicateKeyColumnsRelatedTables AS duplicateKeyColumnsRelatedTablesSQ
             WHERE duplicateKeyColumnsRelatedTablesSQ.columnName = #duplicateKeyColumnsRelatedTables.columnName AND
                   duplicateKeyColumnsRelatedTablesSQ.[FK] IS NULL
             HAVING COUNT(*) <= 1
            )
-- for each column listed, one of the related tables, with PK containing the column name
-- on its list, is the root of information. When you find the root (parentTable), all the
-- other tables without FKs containing the column are the the tables missing FKs on them
SELECT *
FROM #duplicateKeyColumnsRelatedTables
WHERE [FK] IS NULL

    Read more...

  • How to list nullable bit columns with no default bound with sp_bindefault

    No Comments

    I generally create bit columns as NOT NULL DEFAULT dbo.BIT_NO, which in turn is a SQL server default which translates to 0. Today, I found out somebody on my team had created lots of bit fields on several tables as NULL with no default set. So I created the SQL statement below, to list these fields and make my life a bit easier. Comments are welcome. The fields I have to take care of is the ones with '*' on DNF column.

    SELECT CASE WHEN dbo.syscomments.text IS NULL THEN '*' ELSE '' END AS [DNF], -- DNF = Default not found
       dbo.sysobjects.name AS tableName,
       dbo.syscolumns.name AS columnName,
       dbo.systypes.name AS typeName,
       dbo.syscolumns.isnullable, 
       REPLACE(dbo.syscomments.text, CHAR(13) + CHAR(10), '\n') AS [default bound with sp_bindefault]
FROM dbo.syscolumns
 INNER JOIN
     dbo.sysobjects ON dbo.syscolumns.id = dbo.sysobjects.id
 INNER JOIN
     dbo.systypes ON dbo.syscolumns.xtype = dbo.systypes.xtype 
 LEFT OUTER JOIN
     dbo.syscomments ON dbo.syscolumns.cdefault = dbo.syscomments.id AND
                        OBJECTPROPERTY(dbo.syscolumns.cdefault, 'IsConstraint') = 0
WHERE (dbo.sysobjects.type = 'U') AND
      (dbo.systypes.name = 'bit') AND
      (dbo.syscolumns.isnullable = 0)
ORDER BY dbo.sysobjects.name,
         dbo.syscolumns.name

    Read more...