Powershell Day 22

 Day 22 SMO programming Part 3

In earlier blog we have discussed about server and database classes, using table clase you can create a table with different options.

 Table class also require support of other classes like “Datatype” class and “Column” class to specify the data type and column name respectively.

 Table Class:

http://msdn.microsoft.com/enus/library/microsoft.sqlserver.management.smo.table.aspx

 Column Class:

http://msdn.microsoft.com/enus/library/microsoft.sqlserver.management.smo.column.aspx

 Datatype class:

http://msdn.microsoft.com/enus/library/microsoft.sqlserver.management.smo.datatype.aspx

 just need to define the Datatype variable

 As we discussed for creating table object need to have database object (it can be $server objects , reference)but server object is must. 

  1. Load the assembly
  2. Create server object
  3. Create database object or create reference of server.databases[“dbname”] variable
  4. Create a table object
  5. Create datatype variables to define those datatype for our table
  6. Create columns object(s) with reference to table and assign the datatype variable
  7. Finally call create() method for table object

 

[System.Reflection.Assembly]::LoadWithPartialName(’Microsoft.SqlServer.SMO’) | Out-Null

$Server1=new-object (“Microsoft.SqlServer.Management.Smo.Server”) ‘Servername\InstanceNm’

 

#we already seen how to create database object in ealier blog here we will use $server1.databases[“db”] variable

$sDatabase1=$server1.databases[“Db1”]

 

#we are assigning db1 means we will be working on DB1 and creating table.

$table1= new-object (“Microsoft.SqlServer.Management.Smo.table”)($sDatabase1,”NewTable”)

 

#now create datatype variable

$datatype_int = [Microsoft.SqlServer.Management.Smo.Datatype]::int

$datatype_varchar = [Microsoft.SqlServer.Management.Smo.Datatype]::varchar(10)

 

#create column objects

$column_id = new-object (“Microsoft.SqlServer.Management.Smo.column”)($table1,”ID”,$datatype_int)

$column_FNm = new-object (“Microsoft.SqlServer.Management.Smo.column”)($table1,”First_Name”,$datatype_varchar)

$column_LNm = new-object (“Microsoft.SqlServer.Management.Smo.column”)($table1,”Last_Name”,$datatype_varchar)

 

#add columns

$table1.columns.add($column_id )

$table1.columns.add($column_FNm)

$table1.columns.add($column_LNm )

 

#finally create a table

$table1.Create()

 

#Verify that the table is created.

$sDatabase1.tables |select name

 

If you see the above example to create an object is little bit of work. But to get the object values just creating an “Server” object is enough.

Eg.

Create a server object and access all the databases or tables in the database As:

#to get the list of all the databases at server” $Server1”

PS C:\> $server1.databases |select name

 

#to get the list of tables from above selected databases (“DB1”]

PS C:\> $server1.databases[“DB1”].tables

Means only server object can be used to access all the properties/methods/object on that server. To create other object we require to create an object for that class. 

Another very important thing is ….. the status of the object and respective values will be of that point when the object is created.

 In above example the $server object is created and we can see that the tables of database –DB1. Now if we see

PS C:\> $server1.databases[“DB1”].tables[“NewTable”].rowcount

0

 And from some other ways if we insert records into this table even though it will shows rowcount 0 as the server object  was created.

Executequerywithresults is very helpful to run any query on particular server.

            $q1= $database.executequerywithresults(“select * from newTable”)

$q1.tables |ft 

 

Finally, as we can create any object we can alter, drop the object with .alter() or drop() methods respectively. – this applies to all the objects- database/table/columns…

About these ads
This entry was posted in Powershell and tagged . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s