Any idea if it's possible to create a procedure in another database using T-SQL alone, where the name of the database is not known up front and has to be read from a table? Kind of like this example:
任何想法是否可以单独使用T-SQL在另一个数据库中创建一个过程,其中数据库的名称是预先知道的并且必须从表中读取?有点像这个例子:
Use [MasterDatabase]
Declare @FirstDatabase nvarchar(100)
Select Top 1 @FirstDatabase=[ChildDatabase] From [ChildDatabases]
Declare @SQL nvarchar(4000)
Declare @CRLF nvarchar(10) Set @CRLF=nchar(13)+nchar(10)
Set @SQL =
'Use [+'@Firstdatabase+']'+@CRLF+
'Go'+@CRLF+
'Create Proc [Test] As Select 123'
Exec (@SQL)
See what I'm trying to do? This example fails because Go is actually not a T-SQL command but it something recognised by the query analyser/SQL management studio and produces an error. Remove the Go and it also fails because Create Proc must be the first line of the script. Arrgg!!
看看我想做什么?此示例失败,因为Go实际上不是T-SQL命令,但是它由查询分析器/ SQL管理工作室识别并产生错误。删除Go也会失败,因为Create Proc必须是脚本的第一行。 Arrgg!
The syntax of T-SQL doesn't allow you do things like this:
T-SQL的语法不允许你这样做:
Create [OtherDatabase].[dbo].[Test]
创建[OtherDatabase]。[dbo]。[测试]
Which is a shame as it would work a treat! You can do that with Select statements, shame it's inconsistent:
这是一种耻辱,因为它会起作用!你可以用Select语句做到这一点,耻辱它是不一致的:
Select * From [OtherDatabase]..[TheTable]
选择*来自[OtherDatabase] .. [TheTable]
Cheers, Rob.
干杯,罗布。
5 个解决方案
#1
18
It's a pain, but this is what I do. I took this from an example I found on sqlteam, I think - you might have some quoting issues with the way I did the indiscriminate REPLACE
:
这是一种痛苦,但这就是我所做的。我从我在sqlteam上找到的一个例子中得到了这个,我想 - 你可能有一些引用问题与我做不加区分的REPLACE的方式:
DECLARE @sql AS varchar(MAX)
DECLARE @metasql as varchar(MAX)
DECLARE @PrintQuery AS bit
DECLARE @ExecQuery AS bit
SET @PrintQuery = 1
SET @ExecQuery = 0
SET @sql =
'
CREATE PROCEDURE etc.
AS
BEGIN
END
'
SET @metasql = '
USE OtherDatabase
EXEC (''' + REPLACE(@sql, '''', '''''') + ''')
'
IF @PrintQuery = 1
PRINT @metasql
IF @ExecQuery = 1
EXEC (@metasql)
#2
8
DECLARE @UseAndExecStatment nvarchar(4000),
@SQLString nvarchar(4000)
SET @UseAndExecStatment = 'use ' + @DBName +' exec sp_executesql @SQLString'
SET @SQLString = N'CREATE Procedure [Test] As Select 123'
EXEC sp_executesql @UseAndExecStatment,
N'@SQLString nvarchar(4000)', @SQLString=@SQLString
#3
1
This is how i have done with Alter Procedure:
这就是我对Alter程序的处理方式:
DECLARE @metasql as varchar(MAX)
DECLARE @sql AS varchar(MAX)
SET @sql =
'ALTER PROCEDURE [dbo].[GetVersion]
AS
BEGIN
SET NOCOUNT ON;
SELECT TOP(1)[Version] from VersionTable
END'
SET @metasql = '
USE MyProdDb
IF (OBJECT_ID(''GetVersion'') IS NOT NULL OR OBJECT_ID(''GetVersion'', ''P'') IS NOT NULL)
BEGIN
EXEC (''' + REPLACE(@sql, '''', '''''') + ''')
END
'
--PRINT @metasql
EXEC (@metasql)
#4
0
You could shell out to osql using xp_cmdshell, I suppose.
我想你可以使用xp_cmdshell向osql发送shell。
#5
0
I dont think this can be done with TSQL.
我不认为这可以用TSQL完成。
You could use an SSIS package that looped the names and connected to the servers dynamically which creates the schema (procs ) you need.
您可以使用SSIS包来循环名称并动态连接到服务器,从而创建所需的模式(过程)。
This is probably what I would do as it means it is all contained within the package.
这可能是我要做的,因为它意味着它都包含在包中。
Configuration can be kept separate by either using a table or external xml file that contained the list of server/databases to deploy the schema to.
可以使用包含要部署架构的服务器/数据库列表的表或外部xml文件来单独配置。
#1
18
It's a pain, but this is what I do. I took this from an example I found on sqlteam, I think - you might have some quoting issues with the way I did the indiscriminate REPLACE
:
这是一种痛苦,但这就是我所做的。我从我在sqlteam上找到的一个例子中得到了这个,我想 - 你可能有一些引用问题与我做不加区分的REPLACE的方式:
DECLARE @sql AS varchar(MAX)
DECLARE @metasql as varchar(MAX)
DECLARE @PrintQuery AS bit
DECLARE @ExecQuery AS bit
SET @PrintQuery = 1
SET @ExecQuery = 0
SET @sql =
'
CREATE PROCEDURE etc.
AS
BEGIN
END
'
SET @metasql = '
USE OtherDatabase
EXEC (''' + REPLACE(@sql, '''', '''''') + ''')
'
IF @PrintQuery = 1
PRINT @metasql
IF @ExecQuery = 1
EXEC (@metasql)
#2
8
DECLARE @UseAndExecStatment nvarchar(4000),
@SQLString nvarchar(4000)
SET @UseAndExecStatment = 'use ' + @DBName +' exec sp_executesql @SQLString'
SET @SQLString = N'CREATE Procedure [Test] As Select 123'
EXEC sp_executesql @UseAndExecStatment,
N'@SQLString nvarchar(4000)', @SQLString=@SQLString
#3
1
This is how i have done with Alter Procedure:
这就是我对Alter程序的处理方式:
DECLARE @metasql as varchar(MAX)
DECLARE @sql AS varchar(MAX)
SET @sql =
'ALTER PROCEDURE [dbo].[GetVersion]
AS
BEGIN
SET NOCOUNT ON;
SELECT TOP(1)[Version] from VersionTable
END'
SET @metasql = '
USE MyProdDb
IF (OBJECT_ID(''GetVersion'') IS NOT NULL OR OBJECT_ID(''GetVersion'', ''P'') IS NOT NULL)
BEGIN
EXEC (''' + REPLACE(@sql, '''', '''''') + ''')
END
'
--PRINT @metasql
EXEC (@metasql)
#4
0
You could shell out to osql using xp_cmdshell, I suppose.
我想你可以使用xp_cmdshell向osql发送shell。
#5
0
I dont think this can be done with TSQL.
我不认为这可以用TSQL完成。
You could use an SSIS package that looped the names and connected to the servers dynamically which creates the schema (procs ) you need.
您可以使用SSIS包来循环名称并动态连接到服务器,从而创建所需的模式(过程)。
This is probably what I would do as it means it is all contained within the package.
这可能是我要做的,因为它意味着它都包含在包中。
Configuration can be kept separate by either using a table or external xml file that contained the list of server/databases to deploy the schema to.
可以使用包含要部署架构的服务器/数据库列表的表或外部xml文件来单独配置。