2

I am running the following on one instance on the server:

SELECT c.*
FROM sys.columns c
WHERE c.object_id = OBJECT_ID(@tablename);

Everything works great.

I would like to run the same statement but point it to another database on the server.

databaseName.dbo.tablename

I've tried the following (and other variations), but it does not work.

SELECT c.*
FROM sys.columns c
WHERE c.object_id = OBJECT_ID('databaseName.dbo.tablename');

Is there a way to get the column information on one database from another?

Code:

SELECT @SQL = @SQL + 'INSERT INTO myTable 
  (ColumnName,ColumnValue,SID,SName,RID,RName)
  VALUES (''' + QUOTENAME(c.name) + ''',NULL,0,NULL,0,NULL);'
            FROM sys.columns c
            WHERE c.object_id = OBJECT_ID(@tablename);

EXEC(@SQL);

Is there a way to do this dynamically? If I had a @DBName parameter that I was passing in.

5

Add in the database you are looking to query from in front of the sys.columns.

SELECT c.*
FROM <other database name>.sys.columns c
WHERE c.object_id = OBJECT_ID(@tablename);
  • Oh man... it has been a long day. So obvious... Thanks! – webdad3 Oct 3 '16 at 21:53
  • slight wrinkle.. Is there a way to do that dynamically? I updated my question with a code snippet. – webdad3 Oct 3 '16 at 22:03
  • 1
    Just be sure that you fully qualify @tablename, otherwise you would end up with (at best) no data, or (at worst) the wrong data. – Nic Oct 3 '16 at 22:05
  • @webdad3 response from @tpet should be marked as an answer as it is a better solution (as 4 other users have agreed). And I'm not saying it because @tpet writes code and drink beer for a beer intelligence project. (LOL). – nam Nov 18 '18 at 17:42
3

You can do this dynamically this way:

-- parameters to the procedure:
DECLARE @dbname sysname = N'master', @tablename sysname = N'spt_values';


DECLARE @sql nvarchar(max),
  @exec nvarchar(max) = QUOTENAME(@dbname) + N'.sys.sp_executesql';

SET @sql = N'--insert SourceDatabase.dbo.myTable(...)
  SELECT QUOTENAME(c.name),NULL,0,NULL,0,NULL 
  FROM sys.columns AS c -- these will be in @dbname
  INNER JOIN sys.all_objects AS t
  ON c.[object_id] = t.[object_id]
  WHERE t.[schema_id] = 1    -- seems you assume everything will be dbo.
  AND t.name = @tablename;'; -- don't need to protect table name

-- magic sauce: this turns into EXEC dbname.sys.sp_executesql @sql...:
EXEC @exec @sql, N'@tablename sysname', @tablename;

This is quite a bit safer, IMHO, than concatenating @dbname into the statement.

If you need to support more than dbo (again, I made an assumption based on all of the queries in the question), that is easy to add:

-- parameters:
DECLARE @dbname     sysname = N'master', 
        @schemaname sysname = N'dbo', 
        @tablename  sysname = N'spt_values';


DECLARE @sql nvarchar(max),
  @exec nvarchar(max) = QUOTENAME(@dbname) + N'.sys.sp_executesql';

SET @sql = N'--insert SourceDatabase.dbo.myTable(...)
  SELECT QUOTENAME(c.name),NULL,0,NULL,0,NULL 
  FROM sys.columns AS c -- these will be in @dbname
  INNER JOIN sys.all_objects AS t
  ON c.[object_id] = t.[object_id]
  INNER JOIN sys.schemas AS s
  ON t.[schema_id] = s.[schema_id]
  WHERE s.name = @schemaname
  AND t.name = @tablename;';

-- magic sauce: this turns into EXEC dbname.sys.sp_executesql @sql...:
EXEC @exec @sql, N'@tablename sysname, @schemaname sysname', @tablename, @schemaname;
0

You could also join system tables in the remote database

DECLARE @DBName sysname;
DECLARE @tablename sysname;
SELECT  @DBName = N'<yourDB>' ,
        @tablename = N'<yourTable>'

DECLARE @SQL NVARCHAR(MAX);
SELECT  @SQL = 'INSERT INTO myTable (ColumnName,ColumnValue,SID,SName,RID,RName)
SELECT QUOTENAME(c.name),NULL,0,NULL,0,NULL
FROM    ' + @DBName + '.sys.tables t
        JOIN ' + @DBName + '.sys.columns c ON t.object_id = c.object_id
WHERE   t.name = ''' + @tablename + '''';

EXEC (@SQL);

In this instance if you had the same table in multiple schemas that you would get incorrect results. You could fix that by also joining to the sys.schemas table with the schema of the table name.

*Updated SQL to be dynamic as requested in updated question

  • 2
    + @DBName + makes me quite nervous. And why not use sp_executesql? At least then you could avoid the SQL injection lurking with @tablename. – Aaron Bertrand Oct 4 '16 at 4:32
  • Op was already using exec and there were zero indications that this would be used in an application, so no concern around possible injection problems. – Nic Oct 4 '16 at 14:23
  • 2
    OP isn't the only one who will learn from this. I don't think it's ok to ignore best practices just because you don't think it matters to the OP. – Aaron Bertrand Oct 4 '16 at 14:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.