retrieve columns information on a different database table

Hi guys!
Here is my situation:
I have a stored procedure in my first database and inside it I need to
retrieve column names of a table located in a different database. The
database name and the table name are provided as sysname parameters to my
stored procedure.
How do I query the INFORMATION_SCHEMA.COLUMNS from a different database?
Kind regards,
ConstantinYou can qualify the INFORMATION_SCHEMA.COLUMNS view with a database name.
Linchi
"Constantin Stan" wrote:
> Hi guys!
> Here is my situation:
> I have a stored procedure in my first database and inside it I need to
> retrieve column names of a table located in a different database. The
> database name and the table name are provided as sysname parameters to my
> stored procedure.
> How do I query the INFORMATION_SCHEMA.COLUMNS from a different database?
>
> Kind regards,
> Constantin
>
>|||Hi Linchi!
How may I qualify the name?
I tried something with @.FullSourceTableName but you may not select from that
name. It should be a table. It seems I missed some tsql classes.
Apreciate your help!
PS: this is my stored procedure:
ALTER PROCEDURE [dbo].[usp_CreateReportLocalizationForObject]
@.SourceDB as sysname,
@.SourceTable as sysname,
@.Culture varchar(10)
AS
BEGIN
-- i'm looking for something like this. but it doesn't work
declare @.FullSourceTableName sysname
set @.FullSourceTableName =OBJECT_NAME(OBJECT_ID('INFORMATION_SCHEMA.COLUMNS'), DB_ID(@.SourceDB));
print @.FullSourceTableName
-- open cursor to loop the columns list returned as a resultset
declare ColumnsList cursor for (select COLUMN_NAME from
INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @.SourceTable)
open ColumnsList
-- loop records
declare @.ColumnName sysname
fetch next from ColumnsList into @.ColumnName
while (@.@.FETCH_STATUS = 0)
begin
-- if there is no record for this column, then insert, otherwise do nothing!
-- put a default value for DisplayName and Status
if not exists(select * from ReportLocalization where
((ViewName=@.SourceTable) AND (ColumnName=@.ColumnName) AND
(Culture=@.Culture)))
begin
INSERT INTO [ReportLocalization]
(ViewName,[ColumnName],[Culture],[DisplayName],[DisplayCategory],[Status])
VALUES (@.SourceTable, @.ColumnName, @.Culture, @.ColumnName, '', 'active')
end
fetch next from ColumnsList into @.ColumnName
end
-- close cursor and deallocate resources
close ColumnsList
deallocate ColumnsList
-- insert a record for the view name, with ColumnName as empty string, and
default value for DisplayName as SourceTable
if not exists(select * from ReportLocalization where
((ViewName=@.SourceTable) AND (ColumnName='') AND (Culture=@.Culture)))
begin
INSERT INTO [ReportLocalization]
(ViewName,[ColumnName],[Culture],[DisplayName],[DisplayCategory],[Status])
VALUES (@.SourceTable, '', @.Culture, @.SourceTable, '', 'active')
end
-- Return to caller
--
RETURN 0
END
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:A850A318-5925-4BF1-B0C6-D4E3BB64EB05@.microsoft.com...
> You can qualify the INFORMATION_SCHEMA.COLUMNS view with a database name.
> Linchi
> "Constantin Stan" wrote:
>> Hi guys!
>> Here is my situation:
>> I have a stored procedure in my first database and inside it I need to
>> retrieve column names of a table located in a different database. The
>> database name and the table name are provided as sysname parameters to my
>> stored procedure.
>> How do I query the INFORMATION_SCHEMA.COLUMNS from a different database?
>>
>> Kind regards,
>> Constantin
>>