Alternative to Table Variables in SELECT Statement?
-
I just discovered (http://support.microsoft.com/kb/305977) that table variables cannot be used in a SELECT statement.... BUMMER!
Here is what I am trying to do:
1) Loop through a database, gather spaceused information for all tables (that script was already done thanks to a script here: http://www.sqlservercentral.com/scripts/viewscript.asp?scriptid=1774 
2) If any of the tables have a datetime field, I need to find the min and max datetime and put them into the report.
In this example, some of the tables in a database can be horizontally partitioned (Jan06, Feb06, Mar06, etc.). I am trying to go into that table and get the min and max date(s) from the 'mydates' column.
DECLARE @Year varchar(2)
DECLARE @YearAgo varchar(2)
DECLARE @Table varchar(20)
DECLARE @DateVar1 datetime
DECLARE @DateVar2 datetime
SET @Table = 'MAR06'
select @Year = RIGHT((year(getdate())),2)
select @YearAgo = RIGHT((year(getdate())-1),2)
PRINT @Year
PRINT @YearAgo
IF ((LEN(@Table) = 5) AND (@Table LIKE '%'+@Year+'' OR @Table LIKE '%'+@YearAgo+''))
BEGIN
PRINT 'Table is '+@Table
SELECT @DateVar1 = min(MyDates) from @Table
SELECT @DateVar2 = max(MyDates) from @Table
END
-- REAL LIFE this PRINT is a UPDATE INTO Statement
PRINT 'Begin Date: '+@DateVar1+', End Date: '+@DateVar2
I was hoping someone might have a trick up their sleeve to either replace the SELECT Statement or otherwise know of a SP that can get this info?
Then entire code looks like (with credit for the initial script to Felix Garcia Cruz 
: -- DECLARE VARIABLES
DECLARE @Owner varchar(80)
DECLARE @Table varchar(80)
DECLARE @NameTable varchar(80)
DECLARE @DateVar1 varchar(10)
DECLARE @DateVar2 varchar(10)
DECLARE @Year varchar(2)
DECLARE @YearAgo varchar(2)
-- SET VARIABLES
SELECT @Year = RIGHT(YEAR(GETDATE()),2)
SELECT @YearAgo = RIGHT((year(getdate())-1),2)
PRINT @Year
-- CREATE TEMP TABLE
create table #tmpSpace (
Name varchar(60) not null, -- DB Name
Rows int not null, -- Number of Rows
Reserved varchar(20) not null, -- Total Space Reserved (KB)
Data varchar(20) not null, -- Data Space used (KB)
Index_Size varchar(20) not null, -- (KB)
Unused varchar(20) not null, -- Space not used (KB)
BDate datetime null, -- Beginning Date in Table
EDate datetime null -- End Date in Table
)
declare tmpCur insensitive cursor for
select rtrim(so.Name) as 'Table', rtrim(su.Name) as 'Owner'
from sysobjects so (nolock) inner join sysusers su (nolock)
on so.uId = su.uId
where type = 'U'
open tmpCur
fetch next from tmpCur into @Table, @Owner
while @@Fetch_Status = 0 begin
-- SET or RESET Variables
SET @DateVar1 = NULL
SET @DateVar2 = NULL
set @NameTable = @Owner + '.[' + @Table + ']'
-- INSERT SPACEUSED INTO INTO TABLE
insert INTO #tmpSpace (Name, Rows, Reserved, Data, Index_Size, Unused)
exec sp_spaceused @NameTable
-- GET TABLES WITH DATES FOR DATE RANGE
IF ((LEN(@Table) = 5) AND (@Table LIKE '%'+@Year+'' OR @Table LIKE '%'+@YearAgo+''))
BEGIN
PRINT 'Table is '+@Table
SELECT @DateVar1 = min(BillDate) from @Table
SELECT @DateVar2 = max(BillDate) from @Table
END
--UPDATE #tmpSpace SET BDate = @DateVar1, EDate = @DateVar2
fetch next from tmpCur into @Table, @Owner
end
close tmpCur
deallocate tmpCur
update #tmpSpace
set Reserved = left(Reserved, len(Reserved) - 3), Data = left(Data, len(Data) - 3),
Index_Size = left(Index_Size, len(Index_Size) - 3), Unused = left(Unused, len(Unused) - 3)
alter table #tmpSpace
alter column Reserved int
alter table #tmpSpace
alter column Data int
alter table #tmpSpace
alter column Index_Size int
alter table #tmpSpace
alter column Unused int
select Name as DBName, Rows as 'RowCount',
BDate, EDate,
(Reserved/1024) as 'Reserved (MB)',
(Data/1024) as 'Data (MB)',
(Index_Size/1024) as 'Index_size (MB)',
Unused as 'Unused Space (KB)'
from #tmpSpace
order by Name
-- order by Reserved Desc
drop table #tmpSpace
set nocount off
Argue for your limitations, and sure enough they're yours (Richard Bach, Illusions)
-
Do you have to handle multiple date time columns in the tables you are monitoring??
-
No... it is a single datetime column... I just need to find the earliest date (min(MyDates)) and latest date (max(MyDates)) to see how much data is in the table. The data has a lot of attributes, but the one we monitor is the datetime field in terms of how much data is contained in the table(s).
We are not as concerned about the amount of data (# records) as we are the date range of the records.
I think my only solution is to either create another table with a list of all known tables and field(s) that contain datetime columns, or hard code it into my script, since the table name(s) will be the standard for each DB instance.
I just prefer to be a little "dynamic" in creating my scripts... it makes them more versatile and easier to re-use for some other purpose.
Now if only I could become "cursor-less"
Argue for your limitations, and sure enough they're yours (Richard Bach, Illusions)
-
Cursorless is easy. Try this method. Please forgive the syntax I dont have a copy of BOL handy.
/* This will give you all the objects in the current database */
/* You can nest this in a per database loop as well as change */
/* the table to a non-temp table */
DROP
TABLE #TABLES
CREATE
TABLE #TABLES (
TableID int IDENTITY(1,1),
DBName varchar(128) NOT NULL,
SchemaName varchar(128) NOT NULL,
TableName varchar(128)NOT NULL,
HasDateTime bit DEFAULT 0,
DateTimeMax DateTime DEFAULT NULL,
DateTimeMin DateTime DEFAULT NULL
)
INSERT
INTO #TABLES (DBName, SchemaName, TableName)
SELECT
TABLE_CATALOG,
TABLE_SCHEMA,
TABLE_NAME
FROM
INFORMATION_SCHEMA.TABLES
WHERE
TABLE_TYPE = 'BASE TABLE'
SELECT
* FROM #TABLES
DECLARE
@CurrentTableID int, @HighestTableID int
SET @CurrentTableID = (SELECT TOP 1 TableID from #TABLES ORDER BY TableID ASC)
SET
@HighestTableID = (SELECT TOP 1 TableID from #TABLES ORDER BY TableID DESC)
While
@CurrentTableID <= @HighestTableID
BEGIN
DECLARE @DBName varchar(128), @SchemaName varchar(128), @TableName varchar(128)
set @DBName = (select DBName from #TABLES where TableID = @CurrentTableID)
set @SchemaName = (select SchemaName from #TABLES where TableID = @CurrentTableID)
set @TableName = (select TableName from #TABLES where TableID = @CurrentTableID)
UPDATE #TABLES
SET HasDateTime = 1
WHERE TableID = @CurrentTableID
and EXISTS(
SELECT TOP 1
COLUMN_NAME
FROM INFORMATION_SCHEMA.columns
WHERE TABLE_CATALOG = @DBName
and Table_Schema = @SchemaName
and Table_Name =@TableName
and Data_Type = 'datetime')
SET @CurrentTableID = @CurrentTableID + 1
END
SELECT
* FROM #TABLES
This should give you a good idea of how to do it without cursors. The next While loop should include selecting the tables where HasDate = 1 and updating the temp table with the values. I am assuming the column name is static, if its not, just add [DateColumnName] it to the #tables table and use the loop above to set that value as well.
When dealing with dynamic schema/table/column names you can use EXEC(). Just be sure to keep scope in mind when using it (make #Tables an actual table, or make it a Global Temp Table - ##TABLES).
For Example.
EXEC
('UPDATE ##TABLES SET MINDATEVALUE = MAX(u.'+ @MyDateColumn + ') FROM ' + @DBName + '.' + @SchemaName + '.' + @TableName + ' as u WHERE TableID = ' CAST(@TableID as varchar(1000))
-
nope, not your only solution. Table vars don't work in SELECT statements... but table-valued functions do <g>. Even in nested SELECTs and all kinds of other screwy syntax.
>L<
-
Set
NoCount On
Declare
@TableName varchar(50),
@FieldName varchar
(100),
@SqlCmd
nvarchar(1000)
Declare
@TableFieldValues Table
(
[Status] [varchar]
(50) NULL,
[TableName] [sysname]
NOT NULL,
[FieldName] [sysname]
NOT NULL,
[MinFieldValue] [smalldatetime]
NULL,
[MaxFieldValue] [smalldatetime]
NULL
)
Create
Table #TableFieldValues
(
[TableName] [varchar]
(50) NULL,
[FieldName] [varchar]
(50) NULL,
[MinFieldValue] [smalldatetime]
NULL,
[MaxFieldValue] [smalldatetime]
NULL
)
Insert
into @TableFieldValues
Select Null As Status,
so
.name as TableName,
sc
.name as FieldName,
Null As MinFieldValue,
Null As MaxFieldValue
From dbo.sysobjects so
Inner Join dbo.syscolumns sc
on so.id = sc.id
Where so.xtype = 'U'
and sc.xtype In(58,61) -- 58 & 61 are for (SmallDateTime / DateTime)
Order by so.name, sc.name
Set
@TableName = ''
Set
@FieldName = ''
Set
@SqlCmd = ''
While
(Select Count(*) From @TableFieldValues Where Status Is Null) > 0
Begin
Select Top 1
@TableName
= Ltrim(Rtrim(TableName)),
@Fieldname
= Ltrim(Rtrim(FieldName))
From @TableFieldValues
Where Status Is Null
Order By TableName, FieldName
Set @SqlCmd = 'Select ' + '''' + Ltrim(Rtrim(@TableName)) + '''' + ' As TableName, ' + char(13)
Set @SqlCmd = @SqlCmd + '''' + Ltrim(Rtrim(@FieldName)) + '''' + ' As FieldName, ' + char(13)
Set @SqlCmd = @SqlCmd + ' Min(' + Ltrim(Rtrim(@FieldName)) + ') As MinFieldValue, ' + char(13)
Set @SqlCmd = @SqlCmd + ' Max(' + Ltrim(Rtrim(@FieldName)) + ') As MaxFieldValue ' + char(13)
Set @SqlCmd = @SqlCmd + 'From ' + Ltrim(Rtrim(@TableName)) + char(13)
Print @SqlCmd -- Comment out
Insert Into #TableFieldValues
Exec sp_executesql @SqlCmd
Update @TableFieldValues
Set Status = 'Done'
Where TableName = @TableName
And FieldName = @FieldName
End
Select
* from #TableFieldValues
Drop
Table #TableFieldValues
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply