Q: Using single select query get the Minimum,Maximum values of Identity Columns of all the tables in a Database Using a single select query, I need to get the Minimum and Maximum values of Identity Columns along with the other columns specified in the query below, for all tables in a given database.
This is what I've been able to code to get a list of tables and their identity columns:
Select so.name as TableName
,      sic.name as ColumnName
,      i.Rows Count_NumberOfRecords
,      IDENT_CURRENT(so.name)+IDENT_INCR(so.name) as NextSeedValue
from       sys.identity_columns sic
inner join sys.objects          so  on sic.object_id = so.object_id
inner join sys.sysindexes       I   ON So.OBJECT_ID = I.ID
Where so.type_desc = 'USER_TABLE' and last_value is not null and indid IN (0,1);

The query needs to get these extra columns:
MaximumValue (IdentityColumn) and MinimumValue (IdentityColumn) for each table.


A: You should be able to use something along these lines:
DECLARE @cmd NVARCHAR(max);
SET @cmd = '';

SELECT @cmd = @cmd + CASE WHEN (@cmd = '') THEN '' ELSE ' UNION ALL ' END + 'SELECT ''' + 
    QUOTENAME(s.name) + '.' + QUOTENAME(t.name) + ''' AS TableName, ''' + 
    QUOTENAME(c.name) + ''' AS ColumnName, MAX(' + QUOTENAME(c.name) + ') AS MaxID, MIN(' + 
    QUOTENAME(c.name) + ') AS MinID, COALESCE(IDENT_CURRENT(''' + QUOTENAME(s.name) + '.' + 
    QUOTENAME(t.name) + '''),0) + COALESCE(IDENT_INCR(''' + QUOTENAME(s.name) + '.' + 
    QUOTENAME(t.name) + '''),0) AS NextValue FROM ' + QUOTENAME(s.name) + '.' + QUOTENAME(t.name) 
FROM sys.tables t
    INNER JOIN sys.columns c ON t.object_id = c.object_id
    INNER JOIN sys.schemas s on t.schema_id = s.schema_id
WHERE c.is_identity = 1

SELECT @cmd; /* Shows the dynamic query generated, not necessary */

EXEC sp_executesql @cmd;

The query uses dynamic SQL to construct a UNION query that gathers the Table Name, Column Name, and Min and Max ID values currently in every table that has an IDENTITY field.
You could quite easily modify this to show the columns in the format you want, along with the other columns you mention in your question.
I've edited the query above to include the "NextValue" field, however I agree with @AaronBertrand in that this value is of little use, since in a busy system it will most certainly be wrong immediately (or shortly thereafter) once the query executes.
