May 16, 2012 at 1:31 pm
I would like to create a SQL Job that scripts out all the objects in the database, with no data. Can this be done using SQL code? Or I should ask it this way, is there SQL script code that can script all the objects in the database with no data?
The only way that I know is to query each of the system tables.
Thank you in advance
Andrew SQLDBA
May 16, 2012 at 1:57 pm
I prefer to use PowerShell + SMO to do work like this. SMO is what SSMS uses when you right-click an object and choose to script it. The SMO object model offers as many options as you care to explore surrounding how to script a specific object.
Note, it can be done using only T-SQL. Objects are simpler to script out than tables though. Tables and indexes get difficult in a hurry to script out using only T-SQL.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 17, 2012 at 6:59 am
That is what I have been reading, but I am not sure how to code things like that. I can use C#, but then it is not in SQL, and I do not have a way to schedule the code to execute.
Can you guide me to a good resource for beginning PS? I started playing around with it yesterday. All of the resources that I have found online are incomplete, so the sample code does not work.
Thanks
Andrew SQLDBA
May 17, 2012 at 7:17 am
just so happens that in a previous role I was tasked with doing just that so I went for PS
This was my script (changed to prevent servername and databasename from showing)
#ADD THE SQL SNAPINS
Add-PSSnapin SqlServerProviderSnapin100
Add-PSSnapin SqlServerCmdletSnapin100
#MAP NETWORK DRIVE
New-PSDrive -Name A -PSProvider FileSystem -Root \\someserver\someshare
#REMOVE OLD COPIES
get-childitem A:\ -include *.sql -recurse | foreach ($_) {remove-item $_.fullname}
#DECLARE TIMESTAMP FOR THE FILES
$timestamp = Get-Date -Format yyyy-MM-dd-hh_mm
#DO THE SCRIPTING
SL SQLSERVER:\SQL\server\DEFAULT\DATABASES\database\Tables
dir | %{$_.Script() | Out-File "A:\Tables $timestamp.sql" -Append}
SL SQLSERVER:\SQL\server\DEFAULT\DATABASES\database\StoredProcedures
dir | %{$_.Script() | Out-File "A:\StoredProcedures $timestamp.sql" -Append}
SL SQLSERVER:\SQL\server\DEFAULT\DATABASES\database\Views
dir | %{$_.Script() | Out-File "A:\Views $timestamp.sql" -Append}
SL SQLSERVER:\SQL\server\DEFAULT\DATABASES\database\UserDefinedFunctions
dir | %{$_.Script() | Out-File "A:\UserDefinedFunctions $timestamp.sql" -Append}
SL SQLSERVER:\SQL\server\DEFAULT\DATABASES\database\Defaults
dir | %{$_.Script() | Out-File "A:\Defaults $timestamp.sql" -Append}
SL SQLSERVER:\SQL\server\DEFAULT\DATABASES\database\Triggers
dir | %{$_.Script() | Out-File "A:\Triggers $timestamp.sql" -Append}
#REMOVE THE NETWORK DRIVE
Remove-PSDrive -Name A
Its a good starting point and I hope you can follow it enough with the bits of comments that are in the script.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply