January 7, 2015 at 4:15 pm
Hi,
I need to insert FK information into a temp table using database name and table name as a parameter.
I've been trying different ways, even with global temp table, but still doesn't work. Below is the code showing what I am trying to achieve. I want to avoid global temp table if possible.
Any ideas?
Thanks.
IF OBJECT_ID('tempdb..##FKs') IS NOT NULL DROP TABLE ##FKs
CREATE TABLE ##FKs (
[ForeginKeyName] [nvarchar](128) NULL,
[TableSchema] [sysname] NULL,
[TableName] [nvarchar](128) NULL,
[RelatedTableSchema] [sysname] NULL,
[RelatedTable] [nvarchar](128) NULL,
[RelatedColumnName] [sysname] NULL,
[RelatedKeyField] [sysname] NULL
)
declare @dbname sysname = N'MyDB', @tbname sysname = N'MyTable'
declare @s-2 nvarchar(max)
select @s-2 = 'USE ' + @dbname + '
GO
insert into ##FKs([ForeginKeyName], [TableSchema], [TableName], [RelatedTableSchema], [RelatedTable], [RelatedColumnName], [RelatedKeyField])
select distinct object_name(k.constraint_object_id) ForeginKeyName,
(select s2.name from ' + @dbname + '.sys.schemas s2
join ' + @dbname + '.sys.tables t2 on s2.schema_id = t2.schema_id and t2.object_id = k.Parent_Object_id) TableSchema,
object_name(k.Parent_Object_id) TableName,
s.name RelatedTableSchema,
object_name(k.referenced_object_id) RelatedTable,
c.Name RelatedColumnName,
rc.name RelatedKeyField
from ' + @dbname + '.sys.foreign_key_columns k
left join ' + @dbname + '.sys.columns c on object_name(c.object_id) = object_name(k.Parent_Object_id) and c.column_id = k.parent_column_id
left join ' + @dbname + '.sys.columns rc on object_name(rc.object_id) = object_name(k.referenced_object_id) and rc.column_id = k.referenced_column_id
left join ' + @dbname + '.sys.tables t on t.object_id = k.referenced_object_id
left join ' + @dbname + '.sys.schemas s on t.schema_id = s.schema_id
where object_name(k.referenced_object_id) = ''' + @tbname + ''''
select @s-2
select * from ##FKs
ForeginKeyName,TableSchema,TableName,RelatedTableSchema,RelatedTable,RelatedColumnName,RelatedKeyField
(0 row(s) affected)
January 8, 2015 at 1:50 am
What's the goal of your action ?
The use of temp objects is fearsome, because - I assume, you'll be dropping and re-creating the DRI in the process. If that fails, where is your temp data :ermm:
Have you tried using Powershell and SMO ? ( you may miss some options ( version dependant ) when building your own tsql version )
Drop and Re-Create All Foreign Key Constraints in a SQL Server database using PowerShell[/url]
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
January 8, 2015 at 10:01 am
January 8, 2015 at 2:49 pm
JeeTee (1/8/2015)
You could use a regular temp table as well, unless you need the temp table to be accessible from other SPIDs, in which case I'd recommend using a persisted table.Or go the powershell route.
I wish... How do I insert data using dynamic SQL into a temp table? It is not visible to the query, unless I create it inside dynamic SQL. In this case it will not be visible to the other queries in the same process.
January 8, 2015 at 2:51 pm
ALZDBA (1/8/2015)
What's the goal of your action ?The use of temp objects is fearsome, because - I assume, you'll be dropping and re-creating the DRI in the process. If that fails, where is your temp data :ermm:
Have you tried using Powershell and SMO ? ( you may miss some options ( version dependant ) when building your own tsql version )
Drop and Re-Create All Foreign Key Constraints in a SQL Server database using PowerShell[/url]
The goal is to drop the FKs, truncate some tables and re-create the FKs with nocheck option (this is to keep the database schema almost the same.
Could you spare some examples how this can be done in Powershell or SMO?
January 8, 2015 at 2:58 pm
if this is to perform cross database maintenance, powershell might be a better option (one which I'm altogether not familiar with). But what I was saying about the temp tables is that you can define a (non-global) temp table at the start of your script/proc, and it can be used INSIDE dynamic sql contexts, as long as they're on the same SPID. The temp tables are instantiated in tempdb and accessible from any database context.
if object_id('tempdb.dbo.#test') is not null drop table #test
create table #test
(
val int
)
exec ('use AdventureWorks2014 insert into #test(val) select 1')
select *
from #test
The opposite of this is not true however. If you were to declare your temp table using/inside dynamic sql, you would NOT be able to access it from outside, and you would "have" to use a global temp table.
--This will fail because #test2 only exists inside the context of the dynamic sql
exec ('
if object_id(''tempdb.dbo.#test2'') is not null drop table #test2
create table #test2
(
val int
)
')
insert into #test2(val) select 1
January 8, 2015 at 3:05 pm
JeeTee (1/8/2015)
if this is to perform cross database maintenance, powershell might be a better option (one which I'm altogether not familiar with). But what I was saying about the temp tables is that you can define a (non-global) temp table at the start of your script/proc, and it can be used INSIDE dynamic sql contexts, as long as they're on the same SPID. The temp tables are instantiated in tempdb and accessible from any database context.
if object_id('tempdb.dbo.#test') is not null drop table #test
create table #test
(
val int
)
exec ('use AdventureWorks2014 insert into #test(val) select 1')
select *
from #test
The opposite of this is not true however. If you were to declare your temp table using/inside dynamic sql, you would NOT be able to access it from outside, and you would "have" to use a global temp table.
--This will fail because #test2 only exists inside the context of the dynamic sql
exec ('
if object_id(''tempdb.dbo.#test2'') is not null drop table #test2
create table #test2
(
val int
)
')
insert into #test2(val) select 1
It looks good, but one thing concerns me: if you look at my query, you will see it has to be dynamic. How do I insert data into a temp table created outside of the dynamic sql statement?
I could do the same thing as in your example using TSQL and it would fail the same way for the reason mentioned above.
January 8, 2015 at 3:05 pm
I'm not sure I follow. The first example allows you to do just that. You can define any sql you want and still insert it into the temp table defined at the top.
For example (and please, PLEASE don't actually use sp_msforeachdb, I just use it here to save code, but it illustrates how you can loop through databases and use an external temp table. See this post for more http://stackoverflow.com/questions/20131930/sp-msforeachdb-only-include-results-from-databases-with-results) you can get all FKs on all databases doing something like this.
if object_id('tempdb.dbo.#stuff') is not null drop table #stuff
create table #stuff
(
FkName nvarchar(128),
db nvarchar(128),
ObjectId int
)
exec sp_msforeachdb 'insert into #stuff (FkName, db, objectid) select FkName = name, ''?'', parent_object_id from ?.sys.foreign_keys'
select *
from #stuff
This is a laborious explanation, but I took your original code, and dropped one of the hash marks off the table (making it local rather than global) and it worked just fine.
January 8, 2015 at 3:33 pm
JeeTee (1/8/2015)
I'm not sure I follow. The first example allows you to do just that. You can define any sql you want and still insert it into the temp table defined at the top.For example (and please, PLEASE don't actually use sp_msforeachdb, I just use it here to save code, but it illustrates how you can loop through databases and use an external temp table. See this post for more http://stackoverflow.com/questions/20131930/sp-msforeachdb-only-include-results-from-databases-with-results) you can get all FKs on all databases doing something like this.
if object_id('tempdb.dbo.#stuff') is not null drop table #stuff
create table #stuff
(
FkName nvarchar(128),
db nvarchar(128),
ObjectId int
)
exec sp_msforeachdb 'insert into #stuff (FkName, db, objectid) select FkName = name, ''?'', parent_object_id from ?.sys.foreign_keys'
select *
from #stuff
This is a laborious explanation, but I took your original code, and dropped one of the hash marks off the table (making it local rather than global) and it worked just fine.
You are right. Apparently they have changed this: you could not insert into a local temp table from a dynamic sql in one of the previous versions.
I've been chasing my own tail all that time: I had a working approach from the beginning.
Thanks.
January 8, 2015 at 4:33 pm
Note that you can also create non-temp tables in the tempdb database. That way the table will be there even if the connection that created it "gets broken" or has some other issue.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
January 9, 2015 at 2:34 am
Roust_m (1/8/2015)
....The goal is to drop the FKs, truncate some tables and re-create the FKs with nocheck option (this is to keep the database schema almost the same.
Could you spare some examples how this can be done in Powershell or SMO?
Never, and I mean NEVER , add fk constraints using the NOCHECK option !!!
ref: http://www.scarydba.com/2010/11/22/do-foreign-key-constraints-help-performance/ ( read the comments ! )
I tweeked the Powershell script I referred to so it performs what you requested:
Make sure you know what this script does and double check it is fail safe !
Test it before you use it !!!
clear-host ;
$TargetServer ='YourServer\YourInstance' ;
$TargetDb = 'YourDb' ;
# Check module SQLPS ( SQL2014 )
if ( !(get-module -name SQLPs ) ) {
# save original location
Push-Location
# SQLPs will set the current location to SQLSERVER:\ !!
# -DisableNameChecking -> avoid remarks abount non-discouverable function names
import-module -name SQLPs -DisableNameChecking | out-null
#reset current location to original location
Pop-Location
}
#Interrupt when errors occur
Trap {
# Handle the error
$err = $_.Exception
write-host $err.Message
while( $err.InnerException ) {
$err = $err.InnerException
write-host $err.Message
};
( $db.EnumTransactions() | where spid -eq $MySPID )
if ( ( $db.EnumTransactions() | where spid -eq $MySPID ) ) {
write-host 'Transactions rolled back ! ' -BackgroundColor Red -ForegroundColor Black ;
try {
$db.ExecuteNonQuery('Rollback Transaction') ;
}
catch {
# Handle the error
$err = $_.Exception
write-host $err.Message
while( $err.InnerException ) {
$err = $err.InnerException
write-host $err.Message
};
}
}
# End the script.
break
}
# Using Windows authenticated connection
$db = get-sqldatabase -serverinstance $TargetServer -name $TargetDb ;
$MySPID = $db.parent.ConnectionContext.ProcessID ;
$AllTbFK = @() ;
# To ensure the statements are generated for execution in the correct database -> IncludeDatabaseContext = $true
$CreateScriptingOptions = new-object Microsoft.SqlServer.Management.Smo.ScriptingOptions
$CreateScriptingOptions.IncludeDatabaseContext = $true
$DropScriptingOptions = new-object Microsoft.SqlServer.Management.Smo.ScriptingOptions
$DropScriptingOptions.IncludeDatabaseContext = $true
$DropScriptingOptions.ScriptDrops = $true
#initialize result arrays
$Drops = @()
$Creates = @()
#Export Results
$Stamp = Get-Date -Format "yyyy_MM_dd_HHmmss" ;
$FileDropDDL = $('{0}\FKDrops_{1}.sql' -f $env:temp, $Stamp ) ;
$FileCreateDDL = $('{0}\FKCreates_{1}.sql' -f $env:temp, $Stamp ) ;
write-host $( 'Scripting FK for [{0}] - [{1}] {2}' -f $Db.Parent.name, $Db.Name, (Get-Date -Format "yyyy-MM-dd HH:mm:ss") ) ;
$tbcount = 0 ;
#Process all tables
foreach ( $Tb in $db.Tables ) {
$tbcount ++
$pct = $tbcount * 100 / $db.Tables.count
Write-Progress -Activity $( 'Progressing [{0}] - [{1}].[{2}]' -f $Db.Parent.name, $tb.Schema, $tb.Name ) -Status "Table" -PercentComplete $pct ;
$FKCreates = @() ;
$FKDrops = @() ;
$fkcount = 0 ;
#Script all FK for Tb ( this needs to be done separately because otherwise it will generate error "Collection changed" !
foreach ( $Fk in $Tb.ForeignKeys ) {
# Because I'm using IncludeDatabaseContext = $true, I have to -join the results
# or object translation will not result in regular text
$fkcount ++
$pct = $fkcount * 100 / $tb.ForeignKeys.count
Write-Progress -Activity $( 'Progressing [{0}] - [{1}].[{2}] - [{3}]' -f $Db.Parent.name, $tb.Schema, $tb.Name, $fk.Name ) -Status "Script" -PercentComplete $pct ;
# just as a precaution first script out the Create DDL
#Script Create DDL and add a semicolon
$FKCreateDDL = $('{0};' -f ( -join $( $Fk.Script( $CreateScriptingOptions ) -join '; ').tostring() )) ;
#Script Drop
$FKDrops += $('{0};' -f $( -join $( $Fk.Script( $DropScriptingOptions ) -join ';' ).tostring() ) ) ;
# Export Create ddl to file as a precaution
$Filename = $('{0}\FKCreates_{1}_Db_{2}_Tb_{3}.{4}_fk_{5}.sql' -f $env:temp, $Stamp, ($Db.Parent.name).Replace('\','_'), $tb.Schema, $tb.Name, $fk.Name ) ;
$FKCreateDDL | Out-File -FilePath $Filename -NoClobber -Encoding default ;
$FKCreates += $FKCreateDDL ;
$AllTbFK += $Fk ;
}
Write-Progress -Activity $( 'Progressing [{0}] - [{1}].[{2}] - [{3}]' -f $Db.Parent.name, $tb.Schema, $tb.Name, $fk.Name ) -Status "Script" -Completed ;
$Creates += $FKCreates ;
$Drops += $FKDrops ;
$FileCreateDDL = $('{0}\FKCreates_{1}.sql' -f $env:temp, $Stamp ) ;
}
if ( $AllTbFK.count -gt 0 ) {
#Encapsulate in transaction to ensure All or nothing !
$db.ExecuteNonQuery('Begin Transaction') ;
write-host $( 'Dropping FK for [{0}] - [{1}] {2}' -f $Db.Parent.name, $Db.Name, (Get-Date -Format "yyyy-MM-dd HH:mm:ss") )
# dropping the scripted FK
$AllTbFK | % { write-verbose $_.name; $_.drop() ; }
$db.ExecuteNonQuery('Commit Transaction') ;
}
write-host $( 'Deleting data [{0}] - [{1}] {2}' -f $Db.Parent.name, $Db.Name, (Get-Date -Format "yyyy-MM-dd HH:mm:ss") )
# Delete all table data
foreach ( $tb in $db.Tables ) {
try {
write-verbose $( 'Truncating [{0}] - [{1}].[{2}]' -f $Db.Parent.name, $tb.Schema, $tb.Name )
$tb.TruncateData() ;
}
catch {
# Handle the error
$err = $_.Exception
write-verbose $err.Message
while( $err.InnerException ) {
$err = $err.InnerException
write-verbose $err.Message
};
# truncate not allowed for Indexed views !
<#
Exception calling "TruncateData" with "0" argument(s): "Truncate data failed for Table 'Production.ProductModelProductDescriptionCulture'. "
Truncate data failed for Table 'Production.ProductModelProductDescriptionCulture'.
An exception occurred while executing a Transact-SQL statement or batch.
Cannot TRUNCATE TABLE 'Production.ProductModelProductDescriptionCulture' because it is being referenced by object 'vProductAndDescription'.
#>
write-verbose $( 'Deleting rows [{0}] - [{1}].[{2}]' -f $Db.Parent.name, $tb.Schema, $tb.Name )
#$tb.RowCount ;
$DeleteSQL = $('Delete from [{0}].[{1}];' -f $tb.Schema, $tb.Name )
$db.ExecuteNonQuery($DeleteSQL) ;
}
}
if ( $FKCreates.count -gt 0 ) {
write-host $( 'Creating FKs for [{0}] - [{1}] {2}' -f $Db.Parent.name, $Db.Name, (Get-Date -Format "yyyy-MM-dd HH:mm:ss") )
# re-create the scripted FK
$db.ExecuteNonQuery($Creates) ;
}
Write-host 'Exporting results to :'
Write-host ' Drop DDL :' $FileDropDDL ;
Write-host ' Create DDL :' $FileCreateDDL ;
$Drops | Out-File -FilePath $FileDropDDL -NoClobber -Encoding default ;
$Creates | Out-File -FilePath $FileCreateDDL -NoClobber -Encoding default ;
if ( ( $db.EnumTransactions() | where spid -eq $MySPID ) ) {
write-host 'Transactions committed ! ' -BackgroundColor yellow -ForegroundColor Black ;
$db.ExecuteNonQuery('Commit Transaction') ;
}
# Launch the files with their default application
#& "$FileDropDDL";
#& "$FileCreateDDL";
Write-host $('Script Ended {0}' -f (Get-Date -Format "yyyy-MM-dd HH:mm:ss") ) -BackgroundColor Yellow -ForegroundColor Black ;
Test it before you use it !!!
Make sure you know what it does and double check it is fail safe !
Test it before you use it !!!
Test it before you use it !!!
Test it before you use it !!!
Test it before you use it !!!
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
January 12, 2015 at 7:55 pm
ALZDBA (1/9/2015)
Never, and I mean NEVER , add fk constraints using the NOCHECK option !!!ref: http://www.scarydba.com/2010/11/22/do-foreign-key-constraints-help-performance/ ( read the comments ! )
This is a replicated database with NOT ALL tables being replicated. How can you keep the FKs in this situation?
January 12, 2015 at 11:49 pm
Roust_m (1/12/2015)
ALZDBA (1/9/2015)
Never, and I mean NEVER , add fk constraints using the NOCHECK option !!!ref: http://www.scarydba.com/2010/11/22/do-foreign-key-constraints-help-performance/ ( read the comments ! )
This is a replicated database with NOT ALL tables being replicated. How can you keep the FKs in this situation?
The FKs are not the problem, the problem is adding the constraint using the NOCHECK option ( and without having them checked in a later faze). In that case the optimizer doesn't fully thrust the constraint and won't be able to use the constraint to the optimal.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
January 13, 2015 at 1:40 am
Roust_m (1/8/2015)
JeeTee (1/8/2015)
if this is to perform cross database maintenance, powershell might be a better option (one which I'm altogether not familiar with). But what I was saying about the temp tables is that you can define a (non-global) temp table at the start of your script/proc, and it can be used INSIDE dynamic sql contexts, as long as they're on the same SPID. The temp tables are instantiated in tempdb and accessible from any database context.
if object_id('tempdb.dbo.#test') is not null drop table #test
create table #test
(
val int
)
exec ('use AdventureWorks2014 insert into #test(val) select 1')
select *
from #test
The opposite of this is not true however. If you were to declare your temp table using/inside dynamic sql, you would NOT be able to access it from outside, and you would "have" to use a global temp table.
--This will fail because #test2 only exists inside the context of the dynamic sql
exec ('
if object_id(''tempdb.dbo.#test2'') is not null drop table #test2
create table #test2
(
val int
)
')
insert into #test2(val) select 1
It looks good, but one thing concerns me: if you look at my query, you will see it has to be dynamic. How do I insert data into a temp table created outside of the dynamic sql statement?
I could do the same thing as in your example using TSQL and it would fail the same way for the reason mentioned above.
Gosh, no. Don't use PoSH for such a thing. Create a Temp Table with just one column in the main proc and then alter that Temp Table to have the columns you want in the dynamic SQL.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply