An Identity Crisis

  • For those still interested, I whipped up a script to get all of the maximum values for any columns within a database that are defined as integers. (It should be easy to modify if you're interested in, say, smallints or tinyints).

    I'm not normally fond of cursors, but I figured it would be alright as this is a "toolbox" type of script, and not necessarily something that you would want to incorporate into a stored proc, for instance.

    If anyone sees improvements that can be made, have at it.

    http://www.sqlservercentral.com/scripts/contributions/1468.asp

     

    TroyK

  • I use the following script to get the tables having identity values....

    SELECT table_name

          ,column_name

          ,IDENT_CURRENT(table_name) as Identity_value

          ,data_type

     FROM information_schema.columns

    WHERE COLUMNPROPERTY(OBJECT_ID(table_name),column_name,'IsIdentity')=1

    ORDER BY table_name

    Why bother with system tables, when you can get the same with schema view and functions?

  • Nice, Ravi!

     

    I agree -- I don't like to query the sytem tables directly unless absolutely necessary. This is a slick solution -- obviously it didn't occur to me to check the system functions.

     

    TroyK

  • There's no need to "cheat" and use the syscolumns and sysobjects tables.  While INFORMATION_SCHEMA doesn't have the information on identity columns, they OBJECTPROPERTY(<objectid>, 'TableHasIdentity') function tells you which tables have an identity column and you can use it in the WHERE clause to filter the tables from INFORMATION_SCHEMA.  You can also use the COLUMNPROPERTY function to test each column.

    I see that a previous post identified that also.  Instead of keeping around a script, I try and code scripts into User-Defined Fucntions (UDF)s so that they're always in the database.  Mine follows.  You'll also need the function udf_SQL_DataTypeString from here:

    http://www.novicksoftware.com/UDFofWeek/Vol1/T-SQL-UDF-Volume-1-Number-18-udf_SQL_DatatypeString.htm

    SET

    ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    CREATE

     FUNCTION [dbo].[udf_Tbl_IdentityTAB] (

          @Table_Name_Pattern sysname = NULL -- Pattern for table

                                     -- names to include or NULL for all

    ) RETURNS TABLE

       -- No SCHEMABINDING due to use of INFORMATION_SCHEMA

    /*

    * Returns a table of information about the identity columns

    * in tables including their column, data type, and the

    * seed, increment, and current value.

    *

    * Example:

    select * FROM dbo.udf_Tbl_IdentityTAB(default)

    * History:

    * When Who Description

    * ------------- ------- -----------------------------------------

    * 2006-04-02    anovick Added IsMsShipped test

    * 2003-04-02    anovick Initial Coding

    *

    * © Copyright 2002, 2006 Andrew Novick http://www.NovickSoftware.com

    * You may use this function in any of your SQL Server databases

    * including databases that you sell, so long as they contain

    * other unrelated database objects. You may not publish this

    * UDF either in print or electronically except where posted by the author.

    ****************************************************************/

    AS RETURN

    SELECT TOP 100 PERCENT WITH TIES

            T.TABLE_SCHEMA

          , T.TABLE_NAME

          , COALESCE(C.COLUMN_NAME, '<not available to you>')

                                  as [COLUMN_NAME]

          , C.DATA_TYPE as DATA_TYPE

          , IDENT_SEED(T.TABLE_NAME) AS Seed

          , IDENT_INCR(T.TABLE_NAME) AS Increment

          , IDENT_CURRENT(T.TABLE_NAME) [Current_Value]

        FROM INFORMATION_SCHEMA.TABLES T

             LEFT OUTER JOIN ( -- LOJ - COLUMN SECURITY COULD LIMIT ACCESS

                  SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME

                          , ORDINAL_POSITION

                          , dbo.udf_SQL_DataTypeString (DATA_TYPE

                                                 , CHARACTER_MAXIMUM_LENGTH

                                                 , NUMERIC_PRECISION

                                                 , NUMERIC_SCALE) AS DATA_TYPE

                         FROM INFORMATION_SCHEMA.[COLUMNS]

                               WHERE 1=COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA

                                                              + '.' + TABLE_NAME)

                                                       , COLUMN_NAME

                                                       , 'IsIdentity')

              ) C

                ON T.TABLE_SCHEMA = C.TABLE_SCHEMA

                AND T.TABLE_NAME = C.TABLE_NAME

    WHERE T.TABLE_TYPE = 'BASE TABLE'

          AND (@Table_Name_Pattern IS NULL

                         OR T.TABLE_NAME LIKE @Table_Name_Pattern)

          AND 1= OBJECTPROPERTY(OBJECT_ID(T.TABLE_NAME),

                                              'TableHasIdentity')

          AND NOT 1=OBJECTPROPERTY(OBJECT_ID(T.TABLE_NAME) ,

                                              'IsMsShipped')

    ORDER BY T.TABLE_SCHEMA, T.TABLE_NAME

  • Hi Andrew,

    I think we also need to see udf_SQL_DataTypeString to make that UDF work.

    --
    Adam Machanic
    whoisactive

  • Adam,

    He had it on his site - #18

    SET QUOTED_IDENTIFIER ON

    SET ANSI_NULLS ON

    GO

    CREATE FUNCTION dbo.udf_SQL_DataTypeString (

    @BaseDataType nvarchar(128) -- base name like int, numeric

    , @Character_Maximum_Length int

    , @Numeric_Precision int

    , @Numeric_Scale int

    ) RETURNS nvarchar(24) -- Data type name like 'numeric (15, 3)'

    WITH SCHEMABINDING

    /*

    * Returns a data type with full length and precision information

    * based on fields originally queried from

    * INFORMATION_SCHEMA.ROUTINES or from SQL_VARIANT_PROPERTIES.

    * This function is intended to help when reporting on functions

    * and about data.

    *

    * Example:

    SELECT ROUTINE_NAME as [Function]

    , dbo.udf_SQL_DataTypeString (Data_Type

    , Character_Maximum_Length, Numeric_Precision, Numeric_Scale)

    as [Data Type] FROM information_schema.routines

    WHERE ROUTINE_TYPE='FUNCTION'

    *

    * © Copyright 2003 Andrew Novick http://www.NovickSoftware.com

    * You may use this function in any of your SQL Server databases

    * including databases that you sell, so long as they contain

    * other unrelated database objects. You may not publish this

    * UDF either in print or electronically.

    * Published in T-SQL UDF of the Week Newsletter Vol 1 #18

    http://www.NovickSoftware.com/UDFofWeek/UDFofWeek.htm

    ****************************************************************/

    AS BEGIN

    RETURN LTRIM(RTRIM(@BaseDataType))

    + CASE WHEN @BaseDataType in ('char', 'varchar'

    , 'nvarchar', 'nchar')

    THEN '('

    + CONVERT (varchar(4)

    , @Character_Maximum_Length)

    + ')'

    WHEN @BaseDataType in ('numeric', 'decimal')

    THEN '('

    + Convert(varchar(4), @Numeric_Precision)

    + ' ' + convert(varchar(4), @Numeric_scale)

    + ')'

    ELSE '' -- empty string

    END

    END

    GRANT EXEC, REFERENCES ON dbo.udf_SQL_DataTypeString to [PUBLIC]

    GO

Viewing 6 posts - 31 through 35 (of 35 total)

You must be logged in to reply to this topic. Login to reply