April 3, 2012 at 1:38 pm
i'm always in the process of collecting data and my next project is to have a daily record of every object in every database.
to start i have a simple query
select run_date = getdate(), @@servername as server_name, database_name = db_name(), a.name object_name, b.name as index_name, a.type_desc, a.create_date, a.modify_date,
b.type_desc as index_type, b.is_unique, b.is_primary_key, b.is_unique_constraint, c.index_id
from sys.objects a inner join sys.indexes b on a.object_id = b.object_id
inner join sys.partitions c on b.object_id = c.object_id
--inner join sys.columns d on d.object_id = a.object_id
--inner join sys.index_columns e on e.object_id = b.object_id
--inner join sys.index_columns f on f.column_id = d.column_id
--inner join sys.columns g on g.column_id = f.column_id
where a.object_id > 100
and a.type_desc = 'USER_TABLE'
normally i would create dozens of dataflow tasks but i want to learn a bit more about SSIS and tried to get it working with a ForEachLoop
i have an execute SQL task to get a list of all user databases and pass it to a variable. this runs OK
next is a foreach loop container.
I tried it with a data flow task but can't seem to get it to return the objects in user databases. only in the master database which is my first connection
April 3, 2012 at 2:40 pm
I get you wanting to learn SSIS but this task seems like a no brainer for the undocumented proc sp_msforeachdb. The problem in using SSIS is you need a different connection for each iteration, or a way to dynamically change the database.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 4, 2012 at 7:28 am
thx, i'll try it
i had some other data collection queries where i tried to use it and it didn't run the way i wanted to
April 16, 2012 at 1:52 pm
tried the ms_foreachdb but for the part database_name = db_name() i only get master instead of the database the index is really in
i guess i have to go back to the manual method. looking to have a daily dump of object metadata so that some other processes i have like looking for unused or missing indexes can link to it and i can filter out data in some of my reports that make them unreadable
April 16, 2012 at 2:02 pm
Can you post what you have so far? My guess is you are closer than you think.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 16, 2012 at 2:06 pm
declare @cmd1 varchar(5000)
set @cmd1 = 'select run_date = getdate(), @@servername as server_name, database_name = db_name(), a.name object_name, b.name as index_name, a.type_desc, a.create_date, a.modify_date,
b.type_desc as index_type, b.is_unique, b.is_primary_key, b.is_unique_constraint, c.index_id
from sys.objects a inner join sys.indexes b on a.object_id = b.object_id
inner join sys.partitions c on b.object_id = c.object_id
--inner join sys.columns d on d.object_id = a.object_id
--inner join sys.index_columns e on e.object_id = b.object_id
--inner join sys.index_columns f on f.column_id = d.column_id
--inner join sys.columns g on g.column_id = f.column_id
where a.object_id > 100
and a.type_desc = ''USER_TABLE'''
exec sp_MSforeachdb @cmd1
same as above
it runs through all the databases but the database_name is reported as master for all of them
April 16, 2012 at 2:11 pm
See you were super close.
Try this:
declare @cmd1 varchar(5000)
set @cmd1 = 'select run_date = getdate(), @@servername as server_name,
database_name = ''?'',
a.name object_name, b.name as index_name, a.type_desc, a.create_date, a.modify_date,
b.type_desc as index_type, b.is_unique, b.is_primary_key, b.is_unique_constraint, c.index_id
from sys.objects a inner join sys.indexes b on a.object_id = b.object_id
inner join sys.partitions c on b.object_id = c.object_id
--inner join sys.columns d on d.object_id = a.object_id
--inner join sys.index_columns e on e.object_id = b.object_id
--inner join sys.index_columns f on f.column_id = d.column_id
--inner join sys.columns g on g.column_id = f.column_id
where a.object_id > 100
and a.type_desc = ''USER_TABLE'''
exec sp_MSforeachdb @cmd1
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 18, 2012 at 2:01 pm
doesn't seem to work through SSIS. i get an error that a temp table doesn't exist
can i use this without a dataflow task?
i'm trying to create a daily dump of all objects in all user databases on all servers. it's going to serve as a record of any table that is recreated on a daily basis, used to join missing index and other reports to filter out data i don't want in the report, etc.
April 18, 2012 at 2:12 pm
Why not just set it up as a sql job and schedule it?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply