/****************************************************************************/
/* Script Name : Get Max Number of NumberType Data from All Tables */
/* Author : Sunsick Yoon */
/* Version : 1.0 */
/* Create Date : 14.04.2015 */
/* Modified Date : 14.04.2015 */
/****************************************************************************/
--SELECT A.name AS TableName, B.name AS ColumnName, C.name AS TypeName, SELECT MAX(
--FROM sys.sysobjects A join sys.all_columns B ON A.id = B.object_id JOIN sys.types C ON B.system_type_id = C.system_type_id AND C.name in ('int', 'smallint', 'bigint')
--WHERE A.type = 'U'
--SELECT '['+SCHEMA_NAME(schema_id)+'].['+name+']', * FROM AdventureWorks2012.sys.tables
use tempdb
GO
-- #1. Drop Temp Table
IF OBJECT_ID('#TempCommand', 'U') IS NOT NULL
DROP TABLE #TempCommand;
IF OBJECT_ID('TempMaxCount', 'U') IS NOT NULL
DROP TABLE TempMaxCount;
-- #2. Create Temp Table
CREATE TABLE #TempCommand
(
cmd NVARCHAR(2000)
)
CREATE TABLE tempdb.dbo.TempMaxCount
(
Seq INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
dbname varchar(50),
SchemaName varchar(50),
TableName VARCHAR(200),
ColumnName VARCHAR(200),
TypeName VARCHAR(200),
-- Command NVARCHAR(2000),
MaxNumber BIGINT
)
----------------------
-- #3. Make Basic Command -- Execute at once
BEGIN
DECLARE @dbname varchar(50)
DECLARE @command varchar(1000)
DECLARE dbname_cursor CURSOR FOR SELECT name from master..sysdatabases
WHERE name not in ('DB_SecurityAudit', 'master', 'model', 'tempdb', 'msdb')
AND (STATUS NOT IN (65568,48))
OPEN dbname_cursor
FETCH next FROM dbname_cursor into @dbname
WHILE @@fetch_status = 0
BEGIN
--SELECT @command = 'USE [' + @dbname + ']' + CHAR(13)
SELECT @command =
'INSERT INTO tempdb.dbo.TempMaxCount (dbname, SchemaName, TableName, ColumnName, TypeName)
SELECT ''' + @dbname + ''' AS dbname, D.name AS SchemaName, A.name AS TableName, B.name AS ColumnName, C.name AS TypeName
FROM ' + @dbname + '.sys.tables A join ' + @dbname + '.sys.all_columns B ON A.object_id = B.object_id JOIN sys.types C ON B.system_type_id = C.system_type_id AND C.name in (''int'', ''smallint'', ''bigint'')
JOIN ' + @dbname + '.sys.schemas D ON A.schema_id = D.schema_id '
INSERT #TempCommand VALUES (@command)
FETCH NEXT FROM dbname_cursor INTO @dbname
END
CLOSE dbname_cursor
DEALLOCATE dbname_cursor
END
GO
-- #4. Get Result for Execution
SELECT * FROM #TempCommand
--DROP TABLE #TempCommand
---------------------------
-- #5. Paste result from #4 Here.
-- Here
---------------------------
-- #5. Get Max Numbers from All Tables -- Execute at once
BEGIN
SET NOCOUNT ON;
DECLARE @I INT, @TOT INT
DECLARE @CNT BIGINT
DECLARE @SQL NVARCHAR(2000)
DECLARE @dbname varchar(50), @SchemaName varchar(50), @TableName VARCHAR(200), @ColumnName VARCHAR(200)
--UPDATE tempdb.dbo.TempMaxCount
--SET
-- Command = 'SELECT @CNT = MAX(' + ColumnName + ') FROM ' + dbname + '.' + TableName + ' WITH(READUNCOMMITTED) '
SET @I = 1
SELECT @TOT = COUNT(*) FROM tempdb.dbo.TempMaxCount
WHILE @I <= @TOT
BEGIN
SELECT @dbname = dbname, @TableName = TableName, @SchemaName = SchemaName, @ColumnName = ColumnName
FROM tempdb.dbo.TempMaxCount WHERE Seq = @I
SET @SQL = 'UPDATE tempdb.dbo.TempMaxCount SET '
SET @SQL = @SQL + ' MaxNumber = (SELECT MAX([' + @ColumnName + ']) FROM [' + @dbname + '].[' + @SchemaName + '].[' + @TableName + '] WITH(READUNCOMMITTED) ) '
SET @SQL = @SQL + ' WHERE SEQ = ' + CONVERT(VARCHAR(20),@I)
EXEC (@SQL)
--PRINT @SQL
SET @I = @I + 1
END
END
-- #6. Retreive Final Result
SELECT * FROM tempdb.dbo.TempMaxCount
Best_Practices_for_Virtualizing_and_Managing_SQL_Server_2012.pdf