[SQL Server] Get Max Number of NumberType Data from All Tables
You can get the max number of number data type from all tables.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
| --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)
)
GO
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
);
GO
----------------------
-- #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 ('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
|
1
2
3
4
5
6
| ---------------------------
-- #5. Paste result from #4 Here.
-- Here
INSERT INTO tempdb.dbo.TempMaxCount (dbname, SchemaName, TableName, ColumnName, TypeName) SELECT 'SingleDBTest' AS dbname, D.name AS SchemaName, A.name AS TableName, B.name AS ColumnName, C.name AS TypeName FROM SingleDBTest.sys.tables A join SingleDBTest.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 SingleDBTest.sys.schemas D ON A.schema_id = D.schema_id
INSERT INTO tempdb.dbo.TempMaxCount (dbname, SchemaName, TableName, ColumnName, TypeName) SELECT 'AdventureWorks2017' AS dbname, D.name AS SchemaName, A.name AS TableName, B.name AS ColumnName, C.name AS TypeName FROM AdventureWorks2017.sys.tables A join AdventureWorks2017.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 AdventureWorks2017.sys.schemas D ON A.schema_id = D.schema_id
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
| ---------------------------
-- #6. 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
-- #7. Retreive Final Result
SELECT * FROM tempdb.dbo.TempMaxCount
|
Done.
There are free games available on these sites should you use the demo software program. Gambling problems can happen to anybody from any walk of life. Your playing goes from a enjoyable, harmless diversion to 우리카지노 an unhealthy obsession with serious consequences. Whether you wager on sports, scratch playing cards, roulette, poker, or slots—in a casino, on the track, or online—a playing problem can strain your relationships, intrude with work, and lead to financial disaster. You could even do stuff you by no means thought you'd, like running up huge money owed and even stealing cash to gamble.
ReplyDelete