SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Stored procedure?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Marteijn
Starting Member

Netherlands
25 Posts

Posted - 11/08/2013 :  06:35:18  Show Profile  Reply with Quote
Hi,

A question... Hope I'm in the right place cause I'm new to this area of SQL.

I have a bunch of tables. More tables will come in this database.

SELECT 'select max(his_datum_ingang), '+ ''''+ table_name + ''' from '+ table_name
FROM mib_historie.information_schema.tables

As a result of that SQL I get all tables:

select max(his_datum_ingang), 'HIS_GWS_SZVOLGFASE' from HIS_GWS_SZVOLGFASE
select max(his_datum_ingang), 'HIS_GWS_SZWERKER' from HIS_GWS_SZWERKER
select max(his_datum_ingang), 'HIS_PIM_TPER' from HIS_PIM_TPER

I only like to see tablename and max(his_datum_ingang) like above.

To find the max(his_datum_ingang) i need to fire both SQL's for all tables within mib_historie.information_schema.tables

Is there a way to do this automatically? Like in a stored procedure?
I want to create a report with Visual Studio and put that report in the Report Manager. Only need to start to the report and get the answer.

Someone can help me with that?

Thanks already!
Marteijn

James K
Flowing Fount of Yak Knowledge

3651 Posts

Posted - 11/08/2013 :  08:22:58  Show Profile  Reply with Quote
quote:
Originally posted by Marteijn

Hi,

A question... Hope I'm in the right place cause I'm new to this area of SQL.

I have a bunch of tables. More tables will come in this database.

SELECT 'select max(his_datum_ingang), '+ ''''+ table_name + ''' from '+ table_name
FROM mib_historie.information_schema.tables

As a result of that SQL I get all tables:

select max(his_datum_ingang), 'HIS_GWS_SZVOLGFASE' from HIS_GWS_SZVOLGFASE
select max(his_datum_ingang), 'HIS_GWS_SZWERKER' from HIS_GWS_SZWERKER
select max(his_datum_ingang), 'HIS_PIM_TPER' from HIS_PIM_TPER

I only like to see tablename and max(his_datum_ingang) like above.

To find the max(his_datum_ingang) i need to fire both SQL's for all tables within mib_historie.information_schema.tables

Is there a way to do this automatically? Like in a stored procedure?
I want to create a report with Visual Studio and put that report in the Report Manager. Only need to start to the report and get the answer.

Someone can help me with that?

Thanks already!
Marteijn

I didn't quite follow what you are asking - but if you meant that you want to create the select statements and execute them in one shot, you can do it as shown below:
declare @sql nvarchar(max);

SELECT @sql =  querystring from (select 'select max(his_datum_ingang), '+ ''''+ table_name + ''' from '+ table_name + ';'
FROM mib_historie.information_schema.tables for xml path('')) s(querystring);
exec sp_executesql @sql;
Go to Top of Page

Marteijn
Starting Member

Netherlands
25 Posts

Posted - 11/08/2013 :  08:59:41  Show Profile  Reply with Quote
Thank you for your answer!

Is it possible to store the results of query into a table?

select max(his_datum_ingang), 'HIS_GWS_SZVOLGFASE' from HIS_GWS_SZVOLGFASE
select max(his_datum_ingang), 'HIS_GWS_SZWERKER' from HIS_GWS_SZWERKER
select max(his_datum_ingang), 'HIS_PIM_TPER' from HIS_PIM_TPER

2013-10-30 15:42:13.953 HIS_GWS_SZVOLGFASE
2013-11-04 15:39:59.137 HIS_GWS_SZWERKER
2013-11-08 06:15:45.037 HIS_PIM_TPER

I want to make a report in Visual Studio for it!


Edited by - Marteijn on 11/08/2013 09:06:33
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 11/08/2013 :  11:08:15  Show Profile  Reply with Quote
yep use insert..select or select into

select * into YourTable
from
(
select max(his_datum_ingang) AS dateval, 'HIS_GWS_SZVOLGFASE' AS TableName from HIS_GWS_SZVOLGFASE
union all
select max(his_datum_ingang), 'HIS_GWS_SZWERKER' from HIS_GWS_SZWERKER
union all
select max(his_datum_ingang), 'HIS_PIM_TPER' from HIS_PIM_TPER
)t



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Edited by - visakh16 on 11/08/2013 11:08:50
Go to Top of Page

Marteijn
Starting Member

Netherlands
25 Posts

Posted - 11/08/2013 :  11:20:33  Show Profile  Reply with Quote
Thanks.. but... there are a lot of tables in MIB_HISTORIE and tables could be
added on daily bases. It needs to be generic.
The query James K provided works perfectly! Those results needs to be imported in
an empty table so I can make a report in SSRS.

Edited by - Marteijn on 11/08/2013 11:21:23
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 11/08/2013 :  12:38:18  Show Profile  Reply with Quote
then extend that to match this


IF OBJECT_ID('TableCounts') IS NULL
CREATE TABLE TableCounts
(
Cnt int,
TableName varchar(100)
)
DECLARE @SQL VARCHAR(MAX)
SELECT @sql =  'insert TableCounts' + CHaR(10) + CHAR(13)+ querystring from (select ' select max(his_datum_ingang), '+ ''''+ table_name + ''' from '+ table_name + ' UNION ALL ' 
FROM information_schema.tables for xml path('')) s(querystring);
exec sp_executesql @sql;


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Marteijn
Starting Member

Netherlands
25 Posts

Posted - 11/13/2013 :  03:27:42  Show Profile  Reply with Quote
Hi,

I get an error when running the script above:

Msg 214, Level 16, State 2, Procedure sp_executesql, Line 1
Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2210 Posts

Posted - 11/13/2013 :  04:53:03  Show Profile  Reply with Quote
DECLARE @SQL NVARCHAR(MAX)

--
Chandu
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2210 Posts

Posted - 11/13/2013 :  05:08:18  Show Profile  Reply with Quote
IF OBJECT_ID('TableCounts') IS NULL
CREATE TABLE TableCounts
(
Cnt int,
TableName varchar(100)
)
DECLARE @SQL NVARCHAR(MAX)
SELECT @sql = 'insert TableCounts' + CHaR(10) + CHAR(13)+ querystring from (select ' select max(his_datum_ingang), '+ ''''+ table_name + ''' from '+ table_name + ' UNION ALL '
FROM information_schema.tables for xml path('')) s(querystring);
SET @sql = LEFT ( @sql, LEN(@sql)-LEN('UNION ALL select'));
exec sp_executesql @sql;

--
Chandu
Go to Top of Page

Marteijn
Starting Member

Netherlands
25 Posts

Posted - 11/13/2013 :  07:10:25  Show Profile  Reply with Quote
USE MIB_HISTORIE
GO

IF OBJECT_ID('dbo.MAXDAT', 'U') IS NOT NULL
DROP TABLE dbo.MAXDAT
GO

CREATE TABLE dbo.MAXDAT (statement nvarchar(max))
GO

declare @sql nvarchar(max);

SELECT @sql = querystring from (select 'select max(his_datum_ingang), '+ ''''+ table_name + ''' from '+ table_name + ';'
FROM mib_historie.information_schema.tables for xml path('')) s(querystring);
SET @sql = LEFT ( @sql, LEN(@sql)-LEN('UNION ALL select'));

insert into dbo.MAXDAT values (@sql)

--SELECT @sql = 'insert TableCounts' + CHaR(10) + CHAR(13)+ querystring from (select ' select max(his_datum_ingang), '+ ''''+ table_name + ''' from '+ table_name + ' UNION ALL '
--FROM information_schema.tables for xml path('')) s(querystring);
--SET @sql = LEFT ( @sql, LEN(@sql)-LEN('UNION ALL select'));

--exec sp_executesql @sql;

The above SQL works. But i can't use the exec after. I get errors on his_datum_ingang.
Table dbo.MAXDAT contains 1 line after exec:

select max(his_datum_ingang), 'HIS_GWS_SZVOLGFASE' from HIS_GWS_SZVOLGFASE;select max(his_datum_ingang), 'HIS_GWS_SZWERKER' from HIS_GWS_SZWERKER;select max(his_datum_ingang), 'HIS_PIM_TPER' from HIS_PIM_TPER; etc. etc. (There are 70 tables).

Whats going wrong to get the values in the table.

Example:
2013-10-30 15:42:13.953 HIS_GWS_SZVOLGFASE
2013-11-04 15:39:59.137 HIS_GWS_SZWERKER
2013-11-08 06:15:45.037 HIS_PIM_TPER

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 11/13/2013 :  07:48:01  Show Profile  Reply with Quote
do you've that column in all of those tables? otherwise it wont work.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Marteijn
Starting Member

Netherlands
25 Posts

Posted - 11/13/2013 :  08:05:19  Show Profile  Reply with Quote
Yes, that column is in all tables there! Its a date field and i want
the max date of every table. The max date and the name of the table, thats
what i like seeing in a new table.

Thank you!
Go to Top of Page

Marteijn
Starting Member

Netherlands
25 Posts

Posted - 11/13/2013 :  08:20:33  Show Profile  Reply with Quote
USE MIB_HISTORIE
GO

IF OBJECT_ID('MIB_METADATA.dbo.MAXDAT', 'U') IS NOT NULL
DROP TABLE MIB_METADATA.dbo.MAXDAT
GO

CREATE TABLE MIB_METADATA.dbo.MAXDAT (statement nvarchar(max))
GO

USE MIB_HISTORIE
GO

declare @sql nvarchar(max);

SELECT @sql = 'insert MIB_METADATA.dbo.MAXDAT' + CHaR(10) + CHAR(13)+ querystring from (select ' select max(his_datum_ingang), '+ ''''+ table_name + ''' from '+ table_name + ' UNION ALL '
FROM information_schema.tables for xml path('')) s(querystring);
SET @sql = LEFT ( @sql, LEN(@sql)-LEN('UNION ALL select'));

insert into MIB_METADATA.dbo.MAXDAT values (@sql)

--exec sp_executesql @sql;

I get an error on exec

In the table it looks like this now:

insert MIB_METADATA.dbo.MAXDAT select max(his_datum_ingang), 'HIS_GWS_SZVOLGFASE' from HIS_GWS_SZVOLGFASE UNION ALL select max(his_datum_ingang), 'HIS_GWS_SZWERKER' from HIS_GWS_SZWERKER UNION ALL select max(his_datum_ingang), 'HIS_PIM_TPER' from HIS_PIM_TPER UNION ALL etc. etc.
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2210 Posts

Posted - 11/14/2013 :  04:29:13  Show Profile  Reply with Quote
Post us back the error message....

--
Chandu
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 11/14/2013 :  05:24:02  Show Profile  Reply with Quote
whats the purpose of this?
insert into MIB_METADATA.dbo.MAXDAT values (@sql)
@sql contains actual query so didnt understand where you're trying to insert that.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Marteijn
Starting Member

Netherlands
25 Posts

Posted - 11/15/2013 :  03:30:39  Show Profile  Reply with Quote
insert into MIB_METADATA.dbo.MAXDAT values (@sql)
that was to see if it really inserted the value into the table

but if i run the below, i get the following error now...

USE MIB_HISTORIE
GO

IF OBJECT_ID('MIB_METADATA.dbo.MAXDAT', 'U') IS NOT NULL
DROP TABLE MIB_METADATA.dbo.MAXDAT
GO

CREATE TABLE MIB_METADATA.dbo.MAXDAT (statement nvarchar(max))
GO

USE MIB_HISTORIE
GO

declare @sql nvarchar(max);

SELECT @sql = 'insert into MIB_METADATA.dbo.MAXDAT' + CHaR(10) + CHAR(13)+ querystring from (select ' select max(his_datum_ingang), '+ ''''+ table_name + ''' from '+ table_name + ' UNION ALL '
FROM information_schema.tables for xml path('')) s(querystring);
SET @sql = LEFT ( @sql, LEN(@sql)-LEN('UNION ALL select'));

exec dbo.sp_executesql @sql;


Msg 213, Level 16, State 1, Line 1
Column name or number of supplied values does not match table definition.


Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 11/15/2013 :  03:34:14  Show Profile  Reply with Quote
You've only single column defined in MIB_METADATA.dbo.MAXDAT but you're trying to insert more than 1 values into it.


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Marteijn
Starting Member

Netherlands
25 Posts

Posted - 11/15/2013 :  04:38:10  Show Profile  Reply with Quote
Thank you guys! It's working! I was staring blind on the exec commando that i
forgot the design of the table ;-)
Thank you for your effort! :-)
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2210 Posts

Posted - 11/15/2013 :  05:10:22  Show Profile  Reply with Quote
quote:
Originally posted by Marteijn

Thank you guys! It's working! I was staring blind on the exec commando that i
forgot the design of the table ;-)
Thank you for your effort! :-)



Welcome

--
Chandu
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 11/15/2013 :  05:56:38  Show Profile  Reply with Quote
quote:
Originally posted by Marteijn

Thank you guys! It's working! I was staring blind on the exec commando that i
forgot the design of the table ;-)
Thank you for your effort! :-)



cool
glad that you got it all sorted!

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000