hi there
i want to write sql statment to do :
copy (table1) to empty (New_talbe1) with indexes and identity
any body can help me!
SELECT TOP 0 * INTO newTable FROM oldTable
That will get you an empty table schema with the identity property set on the correct column.
You will still have to script out indexes manually, or use PowerShell to generate a script.
DBATools has cmdlets to help you with scripting:
hi
In SQL Server Management Studio ( SSMS ) you can right click on the table
Indexes and everything else will come
change the table name to new table name and any other name changes
So which of those fine tools will generate the scripts for all indexes on the table? It's not readily apparent.
Only if you've setup scripting the way you need it. For example, it won't include triggers unless you tell it to.
That's good but not what I was talking about nor what your example will respond to. I'm talking about what gets generated when you right click on a table (as in your example) and select "Script table as". Those settings are controlled under "Tools/Options".
Thanks for the feedback. Just to be sure, notice the "Script Indexes" on your displayed settings is set to "False". I default to setting that to "TRUE".
Whoops, sorry about that, I linked to the main page, meant to link to this:
Here's a basic PowerShell script to script a table plus its indexes:
$options = New-DbaScriptingOption
$options.ScriptSchema = $true
$options.IncludeDatabaseContext = $true
$options.IncludeHeaders = $false
$options.NoCommandTerminator = $false
$options.ScriptBatchTerminator = $true
$options.AnsiFile = $true
$options.Indexes = $true
Get-DbaDbTable -SqlInstance "." -Database test -Table 'dbo.myTable' | Export-DbaScript -FilePath C:\path\file.sql -ScriptingOptionsObject $options
Now that's what I've been looking for. I'm not a general PowerShell user so let me ask you a question. If we leave out the line for "ScriptSchema", will it generate the scripts just for the indexes?
And I wish they'd name these things in a more obvious manner. To me, this has nothing to do with "EXPORT" and everything to do with GenTableScript.
I won't get into the naming - but I can answer your question. No - you need either ScriptSchema set to true or ScriptData set to true. That is a flag to determine the scripting option - either schema or data.
If you are looking for something that scripts out only the indexes then I am not sure there is a cmdlet available. There is Get-DbaHelpIndex but that doesn't output a script to create indexes as far as I can tell.
No, if ScriptSchema is set to false:
Microsoft.SqlServer.Management.Smo.WrongPropertyValueException: At least one of the properties ScriptData, or ScriptSchema must be specified.
The dbatools scripting cmdlets are all based on SMO, I believe there's quite a bit of SMO in many of the cmdlets.
Generally when I create DB scripts I used sqlpackage.exe, it is better than sliced bread. It can also export data as well as schema, creates DB projects and DACPACs, etc., and can deploy them as well.
Good point about sqlpackage.exe - it is also what is utilized in Azure DevOps to deploy (Publish) changes through the pipelines.
Thanks for the info @robert_volk . It doesn't appear to do only what I need and that's to pass a table name to something and have it CORRECTLY generate all of the rowstore indexes. Yeah, I've seen people's attempts using only T-SQL but, when you scroll down through the discussions, you'll see that they made a million errors.
Yep... I know I can go into object explorer and generate them fairly easily and definitely accurately. It's just been a dream to be able to simply call what what MS uses in the object explorer from T-SQL even if it needs to be a call through xp_CmdShell.