October 7, 2011 at 12:30 pm
I am querying metadata across hundreds of DBs and servers to get information on columns which may need their size expanded. I have a great query that does this, and I use the results from that query to generate Alter scripts. I have now found that I need to call a function to get more column info, namely Object_ID() and ColumnProperty(), and am trying to figure out to do this within msForEachDB.
Thanks,
Chris
Learning something new on every visit to SSC. Hoping to pass it on to someone else.
October 7, 2011 at 12:54 pm
What have you tried so far in order to achieve this?
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
October 7, 2011 at 1:02 pm
Object_ID can take the database and schema name as well as the object name.
select OBJECT_ID(N'MyDatabase.dbo.MyObject')
Does that help?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 7, 2011 at 1:57 pm
Here's my query, without the Where clause. I am trying to get figure out if a column is a computer column, and while I have figured out how to do it, it doesn't lend itself directly to use from within msForEachDB.
exec ('use msdb')
declare @command nvarchar(4000)
set @command = '
Select ''?'', R.TABLE_SCHEMA, R.TABLE_NAME, T.TABLE_TYPE, R.COLUMN_NAME, R.DATA_TYPE, R.CHARACTER_MAXIMUM_LENGTH, COLUMNPROPERTY(OBJECT_ID(R.TABLE_NAME), R.COLUMN_NAME,''IsComputed'') From ?.INFORMATION_SCHEMA.COLUMNS R
Inner Join ?.INFORMATION_SCHEMA.TABLES T On R.TABLE_CATALOG = T.TABLE_CATALOG
And R.TABLE_SCHEMA = T.TABLE_SCHEMA
And R.TABLE_NAME = T.TABLE_NAME '
ColumnProperty requires the Object ID of the table in question and the Column Name. I have to call the Object_ID function for a given DB, and use that for input to the ColumnProperty function, also for the same given DB.
I can't get to far outside of Information Schema because this query needs to run across SQL 2000 through 2008, and it currently does.
I have tried several things, but have changed out the code so many times I would have to try it all again to be able to intelligently list it out for you.
I'll get back on this Monday morning.
Thanks,
Chris
Learning something new on every visit to SSC. Hoping to pass it on to someone else.
October 10, 2011 at 9:32 am
OK, simplifying things a bit, I am running this query:
Select Column_Name, OBJECT_ID(TABLE_NAME)
From MSDB.INFORMATION_SCHEMA.COLUMNS
From a different DB than MSDB. With the above query, it runs but returns Null for the Object ID, presumably because the object is not in the current DB.
Select Column_Name, MSDB.OBJECT_ID(TABLE_NAME)
From MSDB.INFORMATION_SCHEMA.COLUMNS
Returns:
Msg 4121, Level 16, State 1, Line 1
Cannot find either column "MSDB" or the user-defined function or aggregate "MSDB.OBJECT_ID", or the name is ambiguous.
Select Column_Name, MSDB..OBJECT_ID(TABLE_NAME)
From MSDB.INFORMATION_SCHEMA.COLUMNS
Returns:
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '.'.
Select Column_Name, MSDB.dbo.OBJECT_ID(TABLE_NAME)
From MSDB.INFORMATION_SCHEMA.COLUMNS
Returns:
Msg 4121, Level 16, State 1, Line 1
Cannot find either column "MSDB" or the user-defined function or aggregate "MSDB.dbo.OBJECT_ID", or the name is ambiguous.
Select Column_Name, OBJECT_ID(MSDB.INFORMATION_SCHEMA.COLUMNS.TABLE_NAME)
From MSDB.INFORMATION_SCHEMA.COLUMNS
Returns a list of column names, but the object ID is Null again.
Select R.Column_Name, OBJECT_ID(R.TABLE_NAME)
From MSDB.INFORMATION_SCHEMA.COLUMNS R
Returns the same thing as above, Null for the object ID.
I am beginning to think that the only way to go is with sysobjects and sys.sysobjects, for the object ID. Have not yet been through the manually gathering Column Properties for either version.
Thanks,
Chris
Learning something new on every visit to SSC. Hoping to pass it on to someone else.
October 10, 2011 at 10:37 am
OK, things are getting a little better. This:
Select R.Column_Name, OBJECT_ID(R.TABLE_CATALOG + '.' + R.TABLE_SCHEMA + '.' + R.TABLE_NAME )
From MSDB.INFORMATION_SCHEMA.COLUMNS R
returns the name and object ID when run from Master.
This though, still returns a Null for the Column Property:
Select R.Column_Name, COLUMNPROPERTY(OBJECT_ID(R.TABLE_CATALOG + '.' + R.TABLE_SCHEMA + '.' + R.TABLE_NAME ), R.COLUMN_NAME, 'Iscomputed')
From MSDB.INFORMATION_SCHEMA.COLUMNS R
Thanks,
Chris
Learning something new on every visit to SSC. Hoping to pass it on to someone else.
October 10, 2011 at 11:21 am
The ColumnProperty function doesn't work outside the scope of the current database.
You'll need to use it in-scope, which usually means dynamic SQL to build the query string with a "USE" command as part of the string.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 11, 2011 at 11:45 am
That was the ticket!
Essentially:
declare @command nvarchar(4000)
set @command = 'Use [?];
Select ''?'', R.TABLE_SCHEMA, R.TABLE_NAME, T.TABLE_TYPE, R.COLUMN_NAME, R.DATA_TYPE, R.CHARACTER_MAXIMUM_LENGTH, COLUMNPROPERTY(OBJECT_ID(R.TABLE_NAME), R.COLUMN_NAME,''IsComputed'') From ?.INFORMATION_SCHEMA.COLUMNS R
Inner Join ?.INFORMATION_SCHEMA.TABLES T On R.TABLE_CATALOG = T.TABLE_CATALOG
And R.TABLE_SCHEMA = T.TABLE_SCHEMA
And R.TABLE_NAME = T.TABLE_NAME
Where clause omitted.
It execs with MSForEachDB and dumps it all into a temp table which I then select from for a uniform set of results I can manipulate, including generating changes scripts for.
Thanks for the help!
Chris
Learning something new on every visit to SSC. Hoping to pass it on to someone else.
October 13, 2011 at 6:11 am
You're welcome.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply