Windows LiveWindows Live™
 
 
Maximo 的个人资料Max - Put It Together照片日志列表更多 工具 帮助

日志
    10月21日

    Connect to a remote SQL Server using SQLPS.

     

    Displaying a list of tables with their rows count:

    1. You need to have installed an instance of SQL Server 2008 (or 2008 R2) in order to use SQLPS.

    2. To connect to another SQL Server remotely, you need to make sure the server(s) are properly registered in your SQL Management Studio (2008/2008-R2). Look in the *“Registered Servers” pane.

    *Hint: You should be able to query SQL Server 2000, and 2005.

    clip_image002

    3. To start using SQLPS, there’s three ways to open it:

    • a. In the Windows Start/Search box, typing SQLPS and then Enter.

    image

    • b. SQL Management Studio, right-click on any of the objects and look for “Start PowerShell”

    image

    image

    4. Start SQLPS session:

    clip_image004

    5. Check your SQL Registered Servers:

    clip_image006

    6. Here’s a very useful one-liner cmdlet to get tables –rowcounts and generate some output:

     

    # - Change directory to the database ‘RemoteSQL01’ – tables folder:

    CD SQLSERVER:\sql\RemoteSQL01\default\databases\NewDB\tables

    dir | Select name, rowcount | FT -auto

    Name             RowCount

    ----                       --------

    Table1                         0

    Table2                        86

    Table3                      107

    #- or send the result to a *.csv file

    dir | Select name, rowcount | Export-Csv -NoTypeInformation C:\temp\RowCount.csv

    ii C:\temp\RowCount.csv #-To open file in Excel

    The first line use the “CD” to change directory to the remote server tables folder. Then, use the “dir” to list all tables. If you need to find more information about that folder then use the “Get-Member” (or the alias “gm”) so you can see that properties and methods you have available at your finger tips.

    Example, use the following approach to save the results on a variable and then use “Get-member”:

     

    CD SQLSERVER:\sql\Mtrinidad-01\default\databases\developer\tab

    # – Change directory to:

    PS SQLSERVER:\sql\Mtrinidad-01\default\databases\developer\tables>

    dir

    Schema Name Created

    ------ ---- -------

    dbo Accounts 4/15/2009 1:48 PM

    dbo Accounts - Local 8/6/2009 1:10 PM

    :

    # – Create variable x to hold “dir” results

    $x = dir

    $x | gm | more

    TypeName: Microsoft.SqlServer.Management.Smo.Table

    Name MemberType Definition

    ---- ---------- ----------

    :

    Alter Method System.Void Alter()

    :

    Create Method System.Void Create()

    :

    Drop Method System.Void Drop()

    :

    Name Property System.String Name {get;set;}

    :

    RowCount Property System.Int64 RowCount {get;}

     

    Then, using the Cmdlet “Select” (short for “Select-Object”), you can work with the properties: “Name” and “RowCount” to be displayed in the PowerShell Console:

    dir | Select name, rowcount …

    Results:

    image 

    Have fun with SQLPS!!

    18:49 | 写入日志

    评论

    请稍候...
    很抱歉，您输入的评论太长。请缩短您的评论。
    您没有输入任何内容，请重试。
    很抱歉，我们当前无法添加您的评论。请稍后重试。
    若要添加评论，需要您的家长授予您相应权限。请求权限
    您的家长禁用了评论功能。
    很抱歉，我们当前无法删除您的评论。请稍后重试。
    您已超过了一天之内允许提供的评论数上限。请在 24 小时后重试。
    因为我们的系统表明您可能在向其他用户提供垃圾评论，您的帐户已禁用了评论功能。如果您认为我们错误地禁用了您的帐户，请联系 Windows Live 支持部门。
    完成下面的安全检查，您提供评论的过程才能完成。
    您在安全检查中键入的字符必须与图片或音频中的字符一致。
    TrinidadMa​ximo 在此页禁用了评论功能。

    引用通告

    引用此项的网络日志
    • 无