September 17, 2015 at 8:16 am
Hi All,
I am trying to use variables to generate the command:
USE DATABASE
GO
Code below:
DECLARE @DBName_Schema varchar(500)
SET @DBName = 'Test'
EXEC ('USE ' + @DBName )
GO
It does not seem to be working. Could you suggest where I am going wrong or suggest an alternative way of doing this?
Any help would be appreciated!
September 17, 2015 at 8:22 am
AarionSSQL (9/17/2015)
Hi All,I am trying to use variables to generate the command:
USE DATABASE
GO
Code below:
DECLARE @DBName_Schema varchar(500)
SET @DBName = 'Test'
EXEC ('USE ' + @DBName )
GO
It does not seem to be working. Could you suggest where I am going wrong or suggest an alternative way of doing this?
Any help would be appreciated!
It probably is working. But I suspect the problem you are facing is that you are expecting the change to affect the host session. It does not. It runs the exec, changes db context, completes and then returns to the host session.
If you include more commands in your EXEC, they should execute in the context of @DBName.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
September 17, 2015 at 8:47 am
As Phil said, but here's a test if you want to try it:
USE tempdb
GO
PRINT 'Start: ' + db_name()
EXEC ('PRINT ''EXEC1: '' + db_name(); USE master; PRINT ''EXEC2: '' + db_name()')
PRINT 'End: ' + db_name()
which gives
Start: tempdb
EXEC1: tempdb
EXEC2: master
End: tempdb
September 17, 2015 at 10:01 am
Thank you both for your help!
This is the code I am trying to run. I need the code to run the code against the database name I assign to the variable. I want to be able to assign a value to the variable: '@DBName_Schema' and the code will then run against that database.
I tried changing the code to change the context but it is still not working.
DECLARE @DBName_Schema varchar(500)
SET @DBName_Schema = 'SYSTEM_Schema'
EXEC ('USE ' + @DBName_Schema ) -- THIS DOES NOT SEEM TO CHANGE THE DATABASE TO WHAT I NEED IT TO
DECLARE @DBName_Schema varchar(500)
SET @DBName_Schema = 'SYSTEM_Schema'
EXEC ('USE ' + @DBName_Schema )
DECLARE @DBName_Metadata varchar(4000)
SET @DBName_Metadata = '[SYSTEM_Metadata].'
DECLARE @sql varchar(4000)
SET @sql = (
'INSERT INTO ' +@DBName_Metadata+ '[Destination].[TableMetaData]
(
[ObjectID]
,[Ranked]
,[TableName]
,[ColumnName]
,[IdentityColumn]
,[ColumnDataType]
,[ColumnLength]
,[ColumnPrecision]
,[TableType]
,[HashByteCalculation]
)
SELECT
t.object_id AS ObjectID
,RANK() OVER
(PARTITION BY t.name ORDER BY c.name DESC) AS Ranked
,sch.name + ''.'' + t.name AS TableName
,c.name AS ColumnName
,
CASE WHEN ic.name <> '''' THEN ''1''
ELSE ''0''
END
AS IdentityColumn
,ty.name AS ColumnDataType
,c.max_length AS ColumnLength
,c.precision AS ColumnPrecision
,t.type_desc AS TableType
,CASE WHEN ty.name = ''varchar'' THEN ''ISNULL('' + c.Name + '',''''NA'''') + ''''|'''' +''
WHEN ty.name = ''int'' THEN ''CAST(ISNULL('' + c.Name + '',''''NA'''') AS VARCHAR) + ''''|'''' +''
WHEN ty.name = ''decimal'' THEN ''ISNULL(CAST('' + c.Name + '' AS VARCHAR),''''NA'''') + ''''|'''' +''
WHEN ty.name = ''numeric'' THEN ''ISNULL(CAST('' + c.Name + '' AS VARCHAR),''''NA'''') + ''''|'''' +''
WHEN ty.name = ''datetime'' THEN ''CAST(ISNULL(LEFT(CONVERT(VARCHAR,'' + c.Name + '', 120), 10),''''NA'''') AS varchar) + ''''|'''' +''
ELSE ''''
END
AS HashByteCalculation
FROM
sys.tables t
JOIN
sys.columns c ON t.object_id = c.object_id
JOIN
sys.schemas sch ON t.schema_id = sch.schema_id
LEFT JOIN
sys.identity_columns ic ON ic.object_id = c.object_id
JOIN
sys.types ty ON ty.system_type_id = c.system_type_id
WHERE
t.type_desc =''USER_TABLE'' AND c.name <> ''HashByteCalculation'' AND sch.name = ''dbo''
ORDER BY
t.name'
)
--SELECT @sql
EXEC (@SQL)
GO
September 17, 2015 at 10:09 am
Apologies for the different user name on the reply - I had sent it from some else computer
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply