3dkLDySd

1--check if the #databases table is already present and then drop it
2IF OBJECT_ID('tempdb..#databases', 'U') IS NOT NULL
3    drop table #databases;
4
5
6--create the temp table as outside the loop
7create table #databases(
8    ID INT IDENTITY,
9    ArtifactID VARCHAR(20) -- not sure of this ID's data type
10)
11
12
13--check if your temp table exists and drop if necessary
14IF OBJECT_ID('tempdb..#temptable', 'U') IS NOT NULL
15    drop table #temptable;
16
17--create the temp table as outside the loop
18create table #temptable(
19    fileSize dec,
20    extractedTextSize dec
21)
22
23--this will allow the population of each database name
24DECLARE @databasename sysname = '' 
25
26-- initialze to 1 so it matches first record in temp table
27DECLARE @LoopOn int = 1; 
28
29--this will be the max  count from table
30DECLARE @MaxCount int = 0; 
31
32--Once this first statement has been run there will now be a number column 
33that is associated with the artificatID. Each database has an area that is 
34--      titled [EDDS'artifactID']. So if the artifactID = 1111111 then the 
35DB would be accessed at [EDDS1111111]
36
37-- do insert here so it adds the ID column
38INSERT INTO #databases(
39    ArtifactID
40)
41SELECT ArtifactID 
42FROM edds.eddsdbo.[Case]
43where name like '%Review%'
44
45-- sets the max number of loops we are going to do
46select @MaxCount = COUNT(*) 
47FROM #databases;
48
49while @LoopOn <= @MaxCount
50    BEGIN
51        -- your table has IDENTITY so select the one for the loop your on 
52(initalize to 1)
53        select @databasename = ArtifactID 
54        FROM #databases
55        WHERE ID = @LoopOn;
56
57        --generate your sql using the @databasename variable, if you want 
58to make 
59        --the database and table names dynamic too then you can use the 
60same formula
61
62        insert into #temptable
63        select SUM(fileSize)/1024/1024/1024, 
64SUM(extractedTextSize)/1024/1024
65        -- dont know/think this will work like this?  If not you have to 
66use dynamic SQL 
67        FROM [EDDS'+cast(@databasename as nvarchar(128))+'].[EDDSDBO]. 
68[Document] ed
69    where ed.CreatedDate >= (select CONVERT(varchar,dateadd(d,- (day(getdate())),getdate()),106))
70
71    -- remove all deletes/etc and just add one to the @LoopOn and it will be selected above based off the ID
72    select @LoopOn += 1
73end
74
75-- Query the final values in the temp table after the iteration is complete
76select filesize+extractedTextSize as Gigs 
77FROM #temptable