Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««1234

An Identity Crisis Expand / Collapse
Author
Message
Posted Monday, March 28, 2005 11:36 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, December 5, 2014 5:54 PM
Points: 1,341, Visits: 805

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




Post #170388
Posted Thursday, March 23, 2006 5:49 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, March 20, 2007 12:30 PM
Points: 24, Visits: 1

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?

Post #267860
Posted Thursday, March 23, 2006 11:23 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, December 5, 2014 5:54 PM
Points: 1,341, Visits: 805

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




Post #268010
Posted Sunday, April 2, 2006 6:54 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, October 31, 2014 12:30 PM
Points: 4, Visits: 113

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

Post #270391
Posted Sunday, April 2, 2006 1:32 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, December 10, 2014 11:06 AM
Points: 1,113, Visits: 706
Hi Andrew,

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



--
Adam Machanic
SQL Server MVP
SQLblog.com: THE SQL Server Blog Spot on the Web
Post #270400
Posted Monday, October 27, 2008 8:49 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, January 26, 2011 1:32 PM
Points: 146, Visits: 327
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
Post #592166
« Prev Topic | Next Topic »

Add to briefcase «««1234

Permissions Expand / Collapse