February 17, 2006 at 5:53 pm
I'm trying to modify an sproc which goes through and copies all the new tables, or tables which have gained new rows, from a remote database. However, it is only working in the current database, because of the way that it checks for identity columns (in order to set the identity_insert property -- due to the awful feature/bug that you can't set it unless there is an identity column in the table).
I presume I need to convert this:
DECLARE columncursor CURSOR FOR
SELECT
column_name
, COLUMNPROPERTY(OBJECT_ID(@tableBareName), COLUMN_NAME, 'IsIdentity')
AS IsIdentity
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @tableBareName
into an indirect version, so I can qualify the information_schema dynamically with a table name --but I cannot figure out how to get the identity info from another database. That is, I don't seem to be clever enough to get the COLUMNPROPERTY function to work against another database.
For tables, I converted a similar cursor to an indirect one, by calling it indirectly and storing the results in a temp table, and then using a cursor against them, like so:
-- Temp table #desttables lists all dbo tables already here in destination db
CREATE TABLE #desttables ([tname] SYSNAME, [oname] SYSNAME)
SET @sql = 'INSERT INTO #desttables SELECT [table_name] AS [tname], [TABLE_SCHEMA] AS [oname]'
+ ' FROM ' + @destdb + '.[INFORMATION_SCHEMA].[TABLES]'
+ ' WHERE TABLE_SCHEMA = ''dbo'' AND TABLE_TYPE = ''BASE TABLE'''
EXEC [dbo].[sp_executesql] @sql
February 17, 2006 at 6:41 pm
I think I figured out an approach. I can run the whole test remotely (like thunking) in the desired db context via sp_executesql, like so:
SET @sql = 'USE ' + @dbname + '; SELECT
column_name
, COLUMNPROPERTY(OBJECT_ID(''' + @tableinfoschema + '''), COLUMN_NAME, ''IsIdentity'')
AS IsIdentity
FROM ' + @dbname + '.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = ''' + @tableinfoschema + ''''
and then pass that to sp_executesql..
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply