CODEWITHSUNDEEP
sqlsql-servert-sql
Fmanin
Fmanin

Reputation: 575

Max(ID) of each table on a database

I am trying to get the maximum value MAX(ID) for each table I have which contains ID on my DB "Table_Example" and one schema_name in specific.

A single example:

SELECT MAX(ID) FROM Schema_name.Table_name1

This retrieve the maximum ID value that is located on Table_name1, but I have 84 tables. I would like to know the max of each table only in one column. This is the code where I am working on currently: I am using information_schema.columns to get the names of the tables automatic and the schema each table belongs to in order to get the whole DB IDs max(id) in one column.

USE TABLE_EXAMPLE
GO
DECLARE @ID NVARCHAR(MAX) --int

SET @ID = (SELECT DISTINCT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_SCHEMA = 'SCHEMA_NAME' AND COLUMN_NAME IN ('ID') AND DATA_TYPE = 'INT')

SELECT @ID FROM (SELECT ('SCHEMA_NAME'+'.'+TABLE_NAME) AS TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_SCHEMA = 'SCHEMA_NAME' AND COLUMN_NAME = 'ID' AND DATA_TYPE='INT') AS W

This Script retrieve wrong data but I think I am a bit closed to get the values, but I am not sure what I am doing wrong. Could someone give me any good approach? Or any better option to get it done?

Upvotes: 3

Views: 7932

Answers (6)

Marcos Silva
Marcos Silva

Reputation: 2019

This script will list all the max ids. It is assuming your first column is the ID, regardless of its name. 

DECLARE @Script AS VARCHAR(MAX) = ''

SELECT @Script = @Script + 'SELECT MAX(' + COLUMN_NAME + ') AS ID FROM ' + c.TABLE_NAME + '  UNION ALL ' + CHAR(13)+CHAR(10)
FROM INFORMATION_SCHEMA.COLUMNS c
INNER JOIN INFORMATION_SCHEMA.TABLES t ON c.TABLE_NAME = t.TABLE_NAME
WHERE c.ORDINAL_POSITION = 1 and t.TABLE_TYPE = 'BASE TABLE' and c.TABLE_SCHEMA = 'dbo' and c.DATA_TYPE = 'int'

SELECT @Script = LEFT(@Script, LEN(@Script) - 12)

EXEC (@Script)

Upvotes: 0

John Cappelletti
John Cappelletti

Reputation: 81930

Perhaps a little dynamic SQL

Edit This will return the Table Name(s) and Max ID in one dataset

Declare @SQL varchar(max) = '>>>'
Select @SQL = @SQL + SQL
 From (
        Select SQL='Union All Select TableName='''+concat('[',Table_Schema,'].[',Table_Name,']')+''',MaxID=max(ID) From '+concat('[',Table_Schema,'].[',Table_Name,'] ') 
          From INFORMATION_SCHEMA.COLUMNS 
          Where Column_Name = 'ID'
      ) A

Set @SQL=Replace(@SQL,'>>>Union All ','')
Exec(@SQL)

Upvotes: 0

M.Ali
M.Ali

Reputation: 69504

CREATE TABLE #MaxValues (SchemaName SYSNAME , TableName SYSNAME , MaxID INT)
GO


Declare @SchemaName SYSNAME = 'dbo'  --<-- Pass you schema name to this variable
       ,@ColumnName SYSNAME = 'ID'   --<-- Column Name
       ,@DataType   SYSNAME = 'INT'  --<-- Data type 



DECLARE @TableName SYSNAME , @SchmaName SYSNAME
      , @Sql NVARCHAR(MAX) , @ColName SYSNAME;

Declare Cur CURSOR LOCAL FAST_FORWARD FOR
SELECT s.name , t.name , c.name 
FROM sys.columns c 
Inner join sys.tables t  on c.object_id = t.object_id
Inner join sys.schemas s on s.schema_id = t.schema_id
Inner join sys.types tp  on tp.user_type_id = c.user_type_id
WHERE s.name = @SchemaName
 AND  c.name = @ColumnName
 AND  tp.name = @DataType

OPEN Cur 
  FETCH NEXT FROM Cur INTO @SchmaName , @TableName , @ColName

WHILE (@@FETCH_STATUS =0)
BEGIN
   SET @Sql = N'INSERT INTO #MaxValues (SchemaName, TableName, MaxID )'
           +  N' SELECT @SchmaName ,@TableName,  MAX(' + QUOTENAME(@ColName) + N') '
           +  N' FROM ' + QUOTENAME(@SchmaName) + '.' + QUOTENAME(@TableName) 

    Exec sp_executesql @Sql 
                      ,N'@SchmaName SYSNAME , @TableName SYSNAME'
                      ,@SchmaName
                      ,@TableName
     FETCH NEXT FROM Cur INTO @SchmaName , @TableName , @ColName
END

CLOSE Cur 
DEALLOCATE Cur 

SELECT * FROM #MaxValues

Upvotes: 0

John Pasquet
John Pasquet

Reputation: 1842

If you are wanting the max value in your identity columns, regardless of the names of those columns, then this is a very simple way of doing it. This will give you the Table Name, the name of the Identity Column, and the max value of that column: 

SELECT sys.tables.name AS [Table Name], 
    sys.identity_columns.name AS [Column Name],
    last_value AS [Last Value]      
FROM sys.identity_columns
    INNER JOIN sys.tables
        ON sys.identity_columns.object_id = sys.tables.object_id
ORDER BY last_value DESC

Upvotes: 6

Paweł Dyl
Paweł Dyl

Reputation: 9143

This enumerate all tables with column Id and MAX value of this ID:

DECLARE @query nvarchar(MAX);
SELECT @query = COALESCE(@query + char(10)+'UNION ALL '+char(10)+'SELECT '''+QUOTENAME(s.name)+'.'+QUOTENAME(T.name)+''' [Table], MAX(Id) [Max] FROM '+QUOTENAME(s.name)+'.'+QUOTENAME(T.name),
'SELECT '''+QUOTENAME(s.name)+'.'+QUOTENAME(T.name)+''' [Table], MAX(Id) [Max] FROM '+QUOTENAME(s.name)+'.'+QUOTENAME(T.name))
FROM sys.schemas S
JOIN sys.tables T ON S.schema_id=T.schema_id
JOIN sys.columns C ON T.object_id=C.object_id
WHERE C.name='Id';
EXEC(@query);

Upvotes: 4

StackUser
StackUser

Reputation: 5398

Try like this,

This would give you the script.

SELECT DISTINCT 'SELECT MAX(' + + COLUMN_NAME + ') as ' + table_name + 'MaxId FROM ' + table_name
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'dbo'
    AND COLUMN_NAME IN ('ID')

Upvotes: 0

Related Questions