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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy