SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


An Identity Crisis


An Identity Crisis

Author
Message
cs_troyk
cs_troyk
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2107 Visits: 973

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





Ravi Prashanth Lobo-275382
Ravi Prashanth Lobo-275382
SSC-Enthusiastic
SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)

Group: General Forum Members
Points: 166 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?


cs_troyk
cs_troyk
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2107 Visits: 973

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





Andrew Novick
Andrew Novick
SSC Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
Points: 28 Visits: 132

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


Adam Machanic
Adam Machanic
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2899 Visits: 714
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
Douglas Osborne-456728
Douglas Osborne-456728
Old Hand
Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)

Group: General Forum Members
Points: 336 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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search