November 29, 2011 at 2:27 pm
Ok here is the backstory on what needs to be created...
We have a Meta database that contains information about the databases on this server...First we have a Databases table that has each database on the server along with a id (identity column)...We then have a Tables table that has every table for each database...In this table we have a table id (identity column), table name, and then database id that each table belongs to...And then we have a Columns table that has a column id (identity column), column name and table id that each column belongs to...
Now for the Database and Tables tables, I manually entered this information...However with this Column table, there are over 1900+ tables and entering these manually is not the way to go...So my supervisor told me that I could probably create a SSIS package with variables and a For Each loop container to populate this table...
My problem is I dont know how to go about it...Could anyone shed some light on how this can be done?
Any insight on this will be greatly appreciated...
November 29, 2011 at 2:30 pm
Use the DMVs.
master.sys.databases
<database>.sys.tables
<database>.sys.columns
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 29, 2011 at 2:40 pm
You can use sp_MSForEachDB to iterate through each database.
November 29, 2011 at 2:43 pm
Microsoft already has all of that information for you to see through the system catalogs. Check the views that Craig mentioned.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
November 29, 2011 at 2:51 pm
I have been using those views...That is what I used to populate the other two tables...But for this table I can do it so easy like I did with the others...
This was the script I was using to populate the other tables...
INSERT INTO dbo.DBTables
( DatabaseID ,
TableName ,
ColumnCount ,
UserAdded ,
DateAdded ,
UserEdited ,
DateEdited ,
Active
)
SELECT 18,
TABLE_NAME,
NULL,
'',
GETDATE(),
NULL,
NULL,
1
FROM DatabaseName.INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
Manually putting the 18 or whatever Id the DatabaseName had in the Database table...That wasnt bad to do manually bc it was only 18 databases that I had to go thru...But populating the Columns table, I would have 1900+ tables to go thru...Not gonna happen!
November 29, 2011 at 2:55 pm
all 1900 plus in one shot:
i hope the 18 is actually the database name and not the ID?
sp_MSForEachDB 'INSERT INTO SpecificDatabase.dbo.DBTables
( DatabaseID ,
TableName ,
ColumnCount ,
UserAdded ,
DateAdded ,
UserEdited ,
DateEdited ,
Active
)
SELECT ''?'',
TABLE_NAME,
NULL,
'''',
GETDATE(),
NULL,
NULL,
1
FROM [?].INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = ''BASE TABLE'' '
Lowell
November 29, 2011 at 3:02 pm
Yeah that 18 is actually the database id in my Databases table...Why?
November 30, 2011 at 10:16 am
I tried running that script but it did not work for me...
Threw me an error saying it couldnt convert 'master' to int....
I dont need master...
November 30, 2011 at 10:20 am
you just have to modify the script a bit: db_id() function , and 'll let you add your own WHERE statement to skip master/tempdb/model/msdb:
sp_MSForEachDB 'INSERT INTO SpecificDatabase.dbo.DBTables
( DatabaseID ,
TableName ,
ColumnCount ,
UserAdded ,
DateAdded ,
UserEdited ,
DateEdited ,
Active
)
SELECT db_id(''?''),
TABLE_NAME,
NULL,
'''',
GETDATE(),
NULL,
NULL,
1
FROM [?].INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = ''BASE TABLE'' '
Lowell
November 30, 2011 at 11:00 am
Im sorry I think I confused you...
The script there is for INSERTING INTO my Tables table...I have already accomplished that...
Now Im trying to INSERT INTO my Columns table...
November 30, 2011 at 11:04 am
asm1212 (11/30/2011)
Im sorry I think I confused you...The script there is for INSERTING INTO my Tables table...I have already accomplished that...
Now Im trying to INSERT INTO my Columns table...
you've never posted the definition of the columns table.
the theory is the same...however,
create a query that gets the data from a single database.
change the query to use [?].INFORMATION_SCHEMA.COLUMNS
instead of just INFORMATION_SCHEMA.COLUMNS,
put it in the EXEC sp_MSForEach command
Lowell
November 30, 2011 at 11:21 am
That is what I did when I said it didnt work for me...I just changed the columns for what the Columns table used and used ? information_schema.columns
it threw me that error about trying to convert 'master' to int...
November 30, 2011 at 11:23 am
see the post i follwoe dup with...if your destination table expects an int instead of the database name, you had to wrap it with db_id(''?'') instead of ''?''
Lowell
November 30, 2011 at 11:33 am
Here is what it would look like when I try to INSERT INTO my Columns table
sp_MSForEachDB 'INSERT INTO DWMeta.dbo.DWColumns
( TableID,
ColumnName ,
DataType ,
IsPrimaryKey,
BusinessDescription,
UserAdded ,
DateAdded ,
UserEdited ,
DateEdited ,
Active
)
SELECT db_id(''?''), -- Would it still be db_id since we are not using database id, we are using table id from where I populated my Tables table
COLUMN_NAME,
DATA_TYPE,
NULL,
NULL,
'''',
GETDATE(),
NULL,
NULL,
1
FROM [?].INFORMATION_SCHEMA.COLUMNS
November 30, 2011 at 11:39 am
you'll need to use the object_id instead of db_id at the table level.
why are you not saving the tablename? if you drop and recreate a table, it will ahve the same name, but a new Id...that can lead to orpahnad data in this metata data kind of stuff.
why are you inserting NULLS on things like isPrimaryKey and stuff?
if you move away from the information_schema views and use the built in sys.columns, you could get the data in a single pass...i assume now you go back and update that as a seperate pass?
sp_MSForEachDB '
INSERT INTO DWMeta.dbo.DWColumns
( TableID,
ColumnName ,
DataType ,
IsPrimaryKey,
BusinessDescription,
UserAdded ,
DateAdded ,
UserEdited ,
DateEdited ,
Active
)
SELECT object_id(TABLE_NAME), -- Would it still be db_id since we are not using database id, we are using table id from where I populated my Tables table
COLUMN_NAME,
DATA_TYPE,
NULL,
NULL,
'',
GETDATE(),
NULL,
NULL,
1
FROM [?].INFORMATION_SCHEMA.COLUMNS
Lowell
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply