.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
 
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
david stephan

Post New Resource Bookmark and Share   

SQL SERVER DATA BASE DICTIONARY

Posted By:Kemal AL GAZZAH       Posted Date: April 30, 2014    Points: 40    Category:    URL: http://www.dotnetspark.com  

SQL SERVER DATA BASE DICTIONARY
 

This code deals about how to display and update the extended properties of a SQL server data base.
Extended properties are an excellent tool to use in order to store comments about the use of each data base object, this is very usefull for the maintenance and the whole life cycle of any software development.

The following code allows to display and update the standard extended property MS_Description for Tables, procedures, views, functions
1) Db_dictionary.aspx
<%@ Page Language="VB" AutoEventWireup="false" CodeFile="Db_dictionary.aspx.vb" Inherits="Db_dictionary" ValidateRequest="false"  %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>Data base Dictionary</title>
    
    <style type="text/css">
        .style1
        {
            width: 355px;
        }
        .style2
        {
            width: 113px;
        }
        .style3
        {
            font-size:x-large;
            text-align:center
            }
    </style>
    
</head>
<body>
    <form id="form1" runat="server">
    <div>
    <table>
    <tr><td colspan="3">
        <asp:Label ID="lbltitle" Text="Data Base Dictionary__SQL SERVER" 
            runat="server" BackColor="#0066FF" CssClass="style3" ForeColor="White"></asp:Label></td></tr>
    <tr>
    <td><asp:label id="lblstr" runat="server" text="Chaine de connexion"></asp:label></td>
    <td><asp:TextBox runat="server" ID="txtconnect" Width="948px"></asp:TextBox> </td>
    <td><asp:LinkButton ID="lnkok" Text="Valider" runat="server"></asp:LinkButton></td>
    </tr>
    <tr>
    <td><asp:Label runat="server" ID="lblv" Text="Version" Visible="false"></asp:Label></td>
    <td><asp:textbox id="txtv" runat="server" readonly="true" TextMode="MultiLine" 
            style="margin-bottom: 0px" Width="942px" Visible="false"></asp:textbox></td>
    </tr>
    </table>
    <table>
    <tr>
    <td valign="top">
    <asp:Panel runat="server" ID="pnllnk" Visible="false" Height="630px">
    <table bgcolor="#3399ff">
    <tr><td><asp:LinkButton ID="lnktbl" Text="Tables" runat="server" ></asp:LinkButton></td></tr>
    <tr><td><asp:LinkButton ID="lnksp" Text="Stored Procedures" runat="server"></asp:LinkButton></td></tr>
    <tr><td><asp:LinkButton ID="lnkview" Text="Views" runat="server"></asp:LinkButton></td></tr>
    <tr><td><asp:LinkButton ID="lnkfn" Text="Functions" runat="server"></asp:LinkButton></td></tr>
    <tr><td><asp:LinkButton ID="lnktrig" Text="Triggers" runat="server"></asp:LinkButton></td></tr>
    </table>
    </asp:Panel>
    </td>
    <td valign="top">
    <asp:Label ID="lblerr" runat="server"></asp:Label>
    <asp:gridview id="gtables" runat="server" AllowPaging="True" 
            DataSourceID="datasource_tables" AllowSorting="True" 
            AutoGenerateColumns="False" AutoGenerateEditButton="True" CellPadding="4" 
            ForeColor="#333333" GridLines="None" Width="530px" PageSize="20">
        <AlternatingRowStyle BackColor="White" />
        <Columns>
            <asp:CommandField ShowSelectButton="True" />
            <asp:TemplateField HeaderText="Table" SortExpression="name">
                <EditItemTemplate>
                    <asp:Label ID="label10" runat="server" Text='<%# Bind("name") %>'></asp:Label>
                </EditItemTemplate>
                <HeaderTemplate>
                    <asp:label ID="lbltable" runat="server" Text="Table"></asp:label>
                    <asp:TextBox ID="iTextlook" runat="server"></asp:TextBox>
                    <asp:LinkButton ID="lnksearch" runat="server" Text="Look.." OnClick="look"></asp:LinkButton>
                </HeaderTemplate>
                <ItemTemplate>
                    <asp:Label ID="Label1" runat="server" Text='<%# Bind("name") %>'></asp:Label>
                </ItemTemplate>
            </asp:TemplateField>
            <asp:TemplateField HeaderText="Description" SortExpression="description">
                <EditItemTemplate>
                    <asp:TextBox ID="TextBox2" runat="server" Text='<%# Bind("description") %>'></asp:TextBox>
                </EditItemTemplate>
                <ItemTemplate>
                    <asp:Label ID="Label2" runat="server" Text='<%# Bind("description") %>'></asp:Label>
                </ItemTemplate>
            </asp:TemplateField>
        </Columns>
        <EditRowStyle BackColor="#2461BF" />
        <FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
        <HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
        <PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" />
        <RowStyle BackColor="#EFF3FB" />
        <SelectedRowStyle BackColor="#D1DDF1" Font-Bold="True" ForeColor="#333333" />
        <SortedAscendingCellStyle BackColor="#F5F7FB" />
        <SortedAscendingHeaderStyle BackColor="#6D95E1" />
        <SortedDescendingCellStyle BackColor="#E9EBEF" />
        <SortedDescendingHeaderStyle BackColor="#4870BE" />
        </asp:gridview>
    <asp:ObjectDataSource ID="datasource_tables" runat="server" 
            SelectMethod="list_objects" TypeName="cDictionary" 
            UpdateMethod="update_add_extended_properties">
        <SelectParameters>
            <asp:ControlParameter ControlID="hstrconnect" Name="strconnect" 
                PropertyName="Value" Type="String" />                 
            <asp:ControlParameter ControlID="htablelook" Name="iobject" PropertyName="Value" 
                Type="String" />
                 <asp:ControlParameter ControlID="hxtype" Name="xtype" PropertyName="Value" 
                Type="String" />                 
        </SelectParameters>
        <UpdateParameters>
            <asp:Parameter Name="name" Type="String" />
            <asp:Parameter Name="description" Type="String" />
            <asp:ControlParameter Name="strconnect" Type="String" controlID="hstrconnect" PropertyName="Value"  />
            <asp:ControlParameter Name="xtype" Type="String" controlID="hxtype" PropertyName="Value"  />
        </UpdateParameters>
        </asp:ObjectDataSource>
    </td>
    <td ></td>
    <td valign="top">
    <table>
    <tr>
    <td><table border="0" cellspacing="0" cellpadding="0" style="width: 404px"><tr><td class="style2">
        <asp:label id="lbltbl" runat="server" 
            text="Table" ForeColor="Black" Visible="False"></asp:label></td>
        <td><asp:Label ID="lblseltbl" runat="server" 
                ></asp:Label></td></tr></table></td>
    </tr>
    <tr>
    <td valign="top">
    <asp:GridView ID="gcolumns" runat="server" AllowPaging="True" 
            DataSourceID="ocolumns" CellPadding="3" 
            GridLines="Horizontal" PageSize="20" BackColor="White" 
            BorderColor="#E7E7FF" BorderStyle="None" BorderWidth="1px" 
            AllowSorting="True" AutoGenerateColumns="False" AutoGenerateEditButton="True">
        <AlternatingRowStyle BackColor="#F7F7F7" />
        <Columns>
            <asp:CommandField ShowSelectButton="True" />
            <asp:TemplateField HeaderText="Colid" SortExpression="colid">
                <EditItemTemplate>
                    <asp:Label ID="LTextBox1" runat="server" Text='<%# Bind("colid") %>'></asp:Label>
                </EditItemTemplate>
                <ItemTemplate>
                    <asp:Label ID="Label1" runat="server" Text='<%# Bind("colid") %>'></asp:Label>
                </ItemTemplate>
            </asp:TemplateField>
            <asp:TemplateField HeaderText="Colonne" SortExpression="Colonne">
                <EditItemTemplate>
                    <asp:Label ID="TextBox2" runat="server" Text='<%# Bind("Colonne") %>'></asp:Label>
                </EditItemTemplate>
                <ItemTemplate>
                    <asp:Label ID="Label2" runat="server" Text='<%# Bind("Colonne") %>'></asp:Label>
                </ItemTemplate>
            </asp:TemplateField>
            <asp:TemplateField HeaderText="Type" SortExpression="Type">
                <EditItemTemplate>
                    <asp:Label ID="TextBox3" runat="server" Text='<%# Bind("Type") %>'></asp:Label>
                </EditItemTemplate>
                <ItemTemplate>
                    <asp:Label ID="Label3" runat="server" Text='<%# Bind("Type") %>'></asp:Label>
                </ItemTemplate>
            </asp:TemplateField>
            <asp:TemplateField HeaderText="Longueur" SortExpression="Longueur">
                <EditItemTemplate>
                    <asp:Label ID="TextBox4" runat="server" Text='<%# Bind("Longueur") %>'></asp:Label>
                </EditItemTemplate>
                <ItemTemplate>
                    <asp:Label ID="Label4" runat="server" Text='<%# Bind("Longueur") %>'></asp:Label>
                </ItemTemplate>
            </asp:TemplateField>
            <asp:TemplateField HeaderText="Description" SortExpression="description">
                <EditItemTemplate>
                    <asp:TextBox ID="TextBox5" runat="server" Text='<%# Bind("description") %>'></asp:TextBox>
                </EditItemTemplate>
                <ItemTemplate>
                    <asp:Label ID="Label5" runat="server" Text='<%# Bind("description") %>'></asp:Label>
                </ItemTemplate>
            </asp:TemplateField>
            <asp:TemplateField SortExpression="name">
                <EditItemTemplate>
                    <asp:hiddenfield ID="TextBox6" runat="server" Value='<%# Bind("name") %>'></asp:Hiddenfield>
                </EditItemTemplate>
                <ItemTemplate>
                    <asp:hiddenfield ID="Label6" runat="server" Value='<%# Bind("name") %>'></asp:hiddenfield>
                </ItemTemplate>
            </asp:TemplateField>
        </Columns>
        <FooterStyle BackColor="#B5C7DE" ForeColor="#4A3C8C" />
        <HeaderStyle BackColor="#4A3C8C" Font-Bold="True" ForeColor="#F7F7F7" />
        <PagerStyle BackColor="#E7E7FF" ForeColor="#4A3C8C" HorizontalAlign="Right" />
        <RowStyle BackColor="#E7E7FF" ForeColor="#4A3C8C" />
        <SelectedRowStyle BackColor="#738A9C" Font-Bold="True" ForeColor="#F7F7F7" />
        <SortedAscendingCellStyle BackColor="#F4F4FD" />
        <SortedAscendingHeaderStyle BackColor="#5A4C9D" />
        <SortedDescendingCellStyle BackColor="#D8D8F0" />
        <SortedDescendingHeaderStyle BackColor="#3E3277" />
        </asp:GridView>
        </td>
        <td valign="top">
        <asp:TextBox ID="txtobject" runat="server" TextMode="MultiLine" ReadOnly="true" 
                Height="600px" Width="400px"></asp:TextBox>
        </td>
        </tr>
        </table>
    <asp:ObjectDataSource ID="ocolumns" runat="server" SelectMethod="list_columns" 
            TypeName="cDictionary" UpdateMethod="update_extendedproperties_columns">
        <SelectParameters>
            <asp:ControlParameter ControlID="htable" Name="itable" 
                PropertyName="Value" Type="String" />
            <asp:ControlParameter ControlID="hstrconnect" Name="strconnect"  PropertyName="Value" Type="String" />
        </SelectParameters>
        <UpdateParameters>
            <asp:Parameter Name="name" Type="String" />
            <asp:Parameter Name="colonne" Type="String" />
            <asp:Parameter Name="description" Type="String" />
           <asp:ControlParameter ControlID="hstrconnect" Name="strconnect"  PropertyName="Value" Type="String" />
        </UpdateParameters>
        </asp:ObjectDataSource>
    </td>
    </tr>
    </table>
    </div>
    <asp:HiddenField ID="hstrconnect" runat="server" />
    <asp:HiddenField ID="htable" runat="server" />
    <asp:HiddenField ID="htablelook" runat="server" />
    <asp:HiddenField ID="hxtype" runat="server" />
    </form>
</body>
</html>

2)Db_Dictionary.aspx.vb
Imports System.Data
'Kamel Gazzah
'29/04/2014
Partial Class Db_dictionary
    Inherits System.Web.UI.Page
    Protected Sub lnkok_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles lnkok.Click
        Me.hstrconnect.Value = Me.txtconnect.Text
        If Me.hstrconnect.Value <> "" Then
            Try
                If cConnetion.GetInstance.initconnection_str(Me.hstrconnect.Value) = True Then
                    Me.pnllnk.Visible = True
                    Me.lblerr.Visible = False
                    Me.lblerr.Text = ""
                    Me.txtv.Text = cDictionary.get_sql_version(Me.hstrconnect.Value)
                    Me.lblv.Visible = True
                    Me.txtv.Visible = True
                Else
                    Me.lblerr.Visible = True
                    Me.lblerr.Text = "Erreur de connexion"
                End If
            Catch ex As Exception
                Me.lblerr.Visible = True
                Me.lblerr.Text = ex.ToString
            End Try
        End If
    End Sub
    Sub bind_tables()
        Me.gtables.DataBind()
    End Sub
    Protected Sub gtables_DataBound(ByVal sender As Object, ByVal e As System.EventArgs) Handles gtables.DataBound
        Try
            CType(gtables.HeaderRow.FindControl("itextlook"), TextBox).Text = htablelook.Value
            ' Me.gcolumns.DataBind()
            Me.lbltbl.Visible = False
            Me.lblseltbl.Text = ""
            gcolumns.Visible = False
            Me.txtobject.Text = ""
        Catch ex As Exception
        End Try
    End Sub
    Protected Sub gtables_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles gtables.SelectedIndexChanged
        Try
            Me.txtobject.Text = ""
            htable.Value = CType(gtables.SelectedRow.FindControl("Label1"), Label).Text
            Me.gcolumns.DataBind()
            Me.gcolumns.Visible = True
            Me.lblseltbl.Text = htable.Value
            Me.lbltbl.Visible = True
            lbltbl.Text = cDictionary.explicit_xtype(hxtype.Value)
            If hxtype.Value = "tr" Then
                Me.lbltbl.Visible = False
                gcolumns.Visible = False
            End If
            If hxtype.Value <> "u" Then
                Me.txtobject.Text = cDictionary.get_object_text(htable.Value, hstrconnect.Value)
            End If
        Catch ex As Exception
            '
        End Try
    End Sub
    Protected Sub look()
        htablelook.Value = CType(gtables.HeaderRow.FindControl("itextlook"), TextBox).Text()
        Me.gtables.DataBind()
    End Sub
    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

    End Sub
    Protected Sub lnktbl_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles lnktbl.Click
        Try
            Me.hxtype.Value = "u"
            bind_tables()
            CType(gtables.HeaderRow.FindControl("lbltable"), Label).Text = "Table"
        Catch ex As Exception
        End Try
    End Sub
    Protected Sub lnksp_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles lnksp.Click
        Try
            Me.hxtype.Value = "p"
            bind_tables()
            CType(gtables.HeaderRow.FindControl("lbltable"), Label).Text = "Procedure"
        Catch ex As Exception
        End Try
    End Sub
    Protected Sub lnkview_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles lnkview.Click
        Try
            Me.hxtype.Value = "v"
            bind_tables()
            CType(gtables.HeaderRow.FindControl("lbltable"), Label).Text = "View"
        Catch ex As Exception
        End Try
    End Sub
    Protected Sub lnkfn_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles lnkfn.Click
        Try
            Me.hxtype.Value = "fn"
            bind_tables()
            CType(gtables.HeaderRow.FindControl("lbltable"), Label).Text = "Function"
        Catch ex As Exception
        End Try
    End Sub
    Protected Sub lnktrig_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles lnktrig.Click
        Try
            Me.hxtype.Value = "tr"
            bind_tables()
            CType(gtables.HeaderRow.FindControl("lbltable"), Label).Text = "Trigger"
        Catch ex As Exception
        End Try
    End Sub
    Protected Sub gcolumns_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles gcolumns.SelectedIndexChanged


    End Sub
End Class
3)cConnection.vb
'Kamel Gazzah
'April,28 2014
Imports Microsoft.VisualBasic
Imports System.Data
Imports System.Data.SqlClient
Imports System.Collections.Generic
Imports System.Drawing
Public Class cConnetion
    Private connection As SqlConnection
    Private commande As SqlCommand
    Private Shared instance As cConnetion = Nothing
    Public Shared Function GetInstance() As cConnetion
        instance = New cConnetion
        Return instance
    End Function
    Public Function initconnection_str(ByVal strconnect As String) As Boolean
        Dim l_bRetour As Boolean = True
        Try
            Me.connection = New System.Data.SqlClient.SqlConnection()
            Me.connection.ConnectionString = strconnect
            If connection.State = ConnectionState.Closed Then connection.Open()
        Catch ex As Exception
            l_bRetour = False
        Finally
            connection.Close()
        End Try
        Return l_bRetour
    End Function
    Public Function proc_to_table(ByVal nompros As String, ByVal param As List(Of SqlClient.SqlParameter), ByVal strconnect As String) As DataTable
        Dim Dt As New DataTable

        Try
            If Me.initconnection_str(strconnect) Then
                If connection.State = ConnectionState.Closed Then connection.Open()
                Dim da As New SqlDataAdapter()
                da.SelectCommand = New SqlCommand()
                da.SelectCommand.Connection = Me.connection
                da.SelectCommand.CommandText = nompros
                da.SelectCommand.CommandType = CommandType.StoredProcedure
                Dim i As New SqlClient.SqlParameter
                da.SelectCommand.Parameters.Clear()
                If param IsNot Nothing Then
                    For Each i In param
                        da.SelectCommand.Parameters.Add(i)
                    Next
                End If
                da.Fill(Dt)
            Else
                Dt = Nothing
            End If
        Catch ex As Exception
            Dt = Nothing
        Finally
            Me.connection.Close()
            Me.connection.Dispose()

        End Try
        Me.connection.Close()
        Me.connection.Dispose()
        Return Dt
    End Function
    Public Function script_to_table(ByVal strconnect As String, ByVal requet As String) As DataTable
        Dim dt As New DataTable
        If initconnection_str(strconnect) And requet <> "" Then
            Try

                Dim da As New SqlDataAdapter()
                If connection.State = ConnectionState.Closed Then connection.Open()

                da.SelectCommand = New SqlCommand()
                da.SelectCommand.Connection = Me.connection
                da.SelectCommand.CommandText = requet
                da.SelectCommand.CommandType = CommandType.Text
                da.Fill(dt)

            Catch ex As Exception

            Finally
                Me.connection.Close()
                Me.connection.Dispose()
            End Try
        Else

        End If
        Me.connection.Close()
        Me.connection.Dispose()
        Return dt
    End Function
    Public Function execute_script(ByVal strconnect As String, ByVal requet As String) As Boolean
        Dim re As Boolean = True
        If initconnection_str(strconnect) And requet <> "" Then
            Try

                If connection.State = ConnectionState.Closed Then connection.Open()
                If commande Is Nothing Then commande = New SqlCommand
                Me.commande.Connection = Me.connection
                Me.commande.CommandText = requet
                Me.commande.CommandType = CommandType.Text
                Me.commande.ExecuteNonQuery()
            Catch ex As Exception
                re = False
            Finally
                Me.connection.Close()
                Me.connection.Dispose()
            End Try
        Else

        End If
        Me.connection.Close()
        Me.connection.Dispose()
        Return re
    End Function
    Public Function exec_proc(ByVal nomproc As String, ByVal param As List(Of SqlClient.SqlParameter), ByVal strconnect As String) As Boolean
        Dim cmd As New SqlCommand
        Dim res As Boolean = True
        If initconnection_str(strconnect) Then
            Try
                If connection.State = ConnectionState.Closed Then connection.Open()

                cmd.Connection = Me.connection
                cmd.CommandType = CommandType.StoredProcedure
                cmd.CommandText = nomproc
                Dim Param_i As SqlClient.SqlParameter
                If param IsNot Nothing Then
                    For Each Param_i In param
                        cmd.Parameters.Add(Param_i)
                    Next
                End If
                cmd.ExecuteNonQuery()
            Catch ex As Exception
                res = False
            Finally
                Me.connection.Close()
                Me.connection.Dispose()
            End Try
        Else
        End If
        Return res
    End Function
    Public Function proc_to_ds(ByVal strconnect As String, ByVal nompros As String, ByVal param As List _
    (Of SqlClient.SqlParameter)) As System.Data.DataSet
        Dim DS As New DataSet
        If initconnection_str(strconnect) Then
            Try
                If connection.State = ConnectionState.Closed Then connection.Open()
                Dim daSales As New SqlDataAdapter()
                daSales.SelectCommand = New SqlCommand()
                daSales.SelectCommand.Connection = Me.connection
                daSales.SelectCommand.CommandText = nompros
                daSales.SelectCommand.CommandType = CommandType.StoredProcedure
                Dim i As SqlClient.SqlParameter

                For Each i In param
                    daSales.SelectCommand.Parameters.Add(i)
                Next
                daSales.Fill(DS)
            Catch ex As Exception
                DS.Dispose()
            Finally
                Me.connection.Close()
                Me.connection.Dispose()
            End Try
        Else
            DS.Dispose()
        End If
        Return DS
    End Function
End Class




4)cDictionary.vb
Imports Microsoft.VisualBasic
Imports System.Data
Imports System.Data.SqlClient
Imports System.Collections.Generic
'Kamel Gazzah
'29/04/2014
Public Class cDictionary
    Public Shared Function list_objects(ByVal strconnect As String, ByVal iobject As String, ByVal xtype As String) As DataTable
        Dim dt As DataTable = Nothing
        Dim strsql As String = ""
        If xtype = "u" Then
            If iobject = "" Then
                strsql = "select o.Name,isnull(d.value,'') description from sysobjects o left outer join " & _
                "    (SELECT *FROM ::fn_listextendedproperty  (NULL,'user','dbo','table',NULL,NULL,DEFAULT)) " & _
                " d on o.name COLLATE Latin1_General_CI_AS=d.objname and d.name='MS_Description' where o.xtype='u' and o.name not like 'dtp%'  "
            Else
                strsql = "select o.Name,isnull(d.value,'') description from sysobjects o left outer join " & _
                    "    (SELECT *FROM ::fn_listextendedproperty  (NULL,'user','dbo','table',NULL,NULL,DEFAULT)) " & _
                    " d on o.name COLLATE Latin1_General_CI_AS=d.objname and d.name='MS_Description' where o.xtype='u' and o.name like '%" & _
                    iobject & "%'"
            End If
        End If
        If xtype = "p" Then
            If iobject Is Nothing Or iobject = "" Then
                strsql = "select o.Name,isnull(d.value,'') description from sysobjects o left outer join " & _
                "    (SELECT *FROM ::fn_listextendedproperty  (NULL,'user','dbo','procedure',NULL,NULL,DEFAULT)) " & _
                "d on o.name COLLATE Latin1_General_CI_AS=d.objname and d.name='MS_Description' where o.xtype='p' and o.name not like 'dt_%' "
            Else
                strsql = "select o.Name,isnull(d.value,'') description from sysobjects o left outer join " & _
                    "    (SELECT *FROM ::fn_listextendedproperty  (NULL,'user','dbo','procedure',NULL,NULL,DEFAULT))" & _
                    " d on o.name COLLATE Latin1_General_CI_AS=d.objname and d.name='MS_Description' where o.xtype='p' and o.name like '%" & _
                    iobject & "%'"
            End If
        End If
        If xtype = "v" Then
            If iobject Is Nothing Or iobject = "" Then
                strsql = "select o.Name,isnull(d.value,'') description from sysobjects o left outer join " & _
                "    (SELECT *FROM ::fn_listextendedproperty  (NULL,'user','dbo','view',NULL,NULL,DEFAULT))" & _
                " d on o.name COLLATE Latin1_General_CI_AS=d.objname and d.name='MS_Description' where o.xtype='v'  "
            Else
                strsql = "select o.Name,isnull(d.value,'') description from sysobjects o left outer join " & _
                    "    (SELECT *FROM ::fn_listextendedproperty  (NULL,'user','dbo','view',NULL,NULL,DEFAULT))" & _
                    " d on o.name COLLATE Latin1_General_CI_AS=d.objname and d.name='MS_Description' where o.xtype='v' and o.name like '%" & _
                    iobject & "%'"
            End If
        End If
        If xtype = "fn" Then
            If iobject Is Nothing Or iobject = "" Then
                strsql = "select o.Name,isnull(d.value,'') description from sysobjects o left outer join " & _
                "    (SELECT *FROM ::fn_listextendedproperty  (NULL,'user','dbo','function',NULL,NULL,DEFAULT)) d on o.name COLLATE Latin1_General_CI_AS=d.objname and d.name='MS_Description' where o.xtype='fn'  "
            Else
                strsql = "select o.Name,isnull(d.value,'') description from sysobjects o left outer join " & _
                    "    (SELECT *FROM ::fn_listextendedproperty  (NULL,'user','dbo','function',NULL,NULL,DEFAULT)) d on o.name COLLATE Latin1_General_CI_AS=d.objname and d.name='MS_Description' where o.xtype='fn' and o.name like '%" & _
                    iobject & "%'"
            End If
        End If
        If xtype = "tr" Then
            If iobject Is Nothing Or iobject = "" Then
                strsql = "select o.Name,isnull(d.value,'') description from sysobjects o left outer join " & _
                "    (SELECT *FROM ::fn_listextendedproperty  (NULL,'user','dbo','trigger',NULL,NULL,DEFAULT)) d on o.name COLLATE Latin1_General_CI_AS=d.objname and d.name='MS_Description' where o.xtype='tr'  "
            Else
                strsql = "select o.Name,isnull(d.value,'') description from sysobjects o left outer join " & _
                    "    (SELECT *FROM ::fn_listextendedproperty  (NULL,'user','dbo','trigger',NULL,NULL,DEFAULT)) d on o.name COLLATE Latin1_General_CI_AS=d.objname and d.name='MS_Description' where o.xtype='tr' and o.name like '%" & _
                    iobject & "%'"
            End If

        End If

        dt = cConnetion.GetInstance.script_to_table(strconnect, strsql)
        Return dt
    End Function
    Public Shared Function explicit_xtype(ByVal xtype As String) As String
        Dim explicit As String = ""
        Select Case xtype
            Case "u" : explicit = "table"
            Case "p" : explicit = "procedure"
            Case "v" : explicit = "view"
            Case "tr" : explicit = "trigger"
            Case "fn" : explicit = "function"
        End Select
        Return explicit
    End Function
    Public Shared Sub update_add_extended_properties(ByVal name As String, ByVal description As String, ByVal xtype As String, ByVal strconnect As String)

        Dim script As String
        script = "if  exists(select 1 from sysobjects where name='usp_update_extended_property') drop proc usp_update_extended_property " & _
                " begin " & _
                " declare @script as nvarchar(4000)" & _
                " set @script='" & _
                " create proc usp_update_extended_property(@iobject nvarchar(1000),@xtype nvarchar(2),@desc sql_variant)" & _
                " as" & _
                " begin" & _
                " if exists(select 1 from sysobjects where xtype=''" & xtype & "'' and name=@iobject)" & _
                " begin" & _
                " if exists(select 1 from ::fn_listextendedproperty(NULL,''user'',''dbo'',''" & explicit_xtype(xtype) & "'',NULL,NULL,DEFAULT) where objname=@iobject)" & _
                " EXECUTE sp_updateextendedproperty N''MS_Description'', @desc, N''user'', N''dbo'', N''" & explicit_xtype(xtype) & "'', @iobject, NULL, NULL" & _
                " Else" & _
                " EXECUTE sp_addextendedproperty N''MS_Description'', @desc, N''user'', N''dbo'', N''" & explicit_xtype(xtype) & "'', @iobject, NULL, NULL" & _
                " End" & _
                " End '" & _
                " exec(@script)" & _
                " End"



        cConnetion.GetInstance.execute_script(strconnect, script)
        Dim p As SqlParameter
        Dim lp As New List(Of SqlParameter)

        p = New SqlParameter("@iobject", SqlDbType.NVarChar)
        p.Value = name
        lp.Add(p)

        p = New SqlParameter("@xtype", SqlDbType.NVarChar)
        p.Value = xtype
        lp.Add(p)

        p = New SqlParameter("@desc", SqlDbType.NVarChar)
        p.Value = description
        lp.Add(p)

        cConnetion.GetInstance.exec_proc("usp_update_extended_property", lp, strconnect)
    End Sub
    Public Shared Function list_columns(ByVal itable As String, ByVal strconnect As String) As DataTable
        Dim script As String
        script = "if exists(select 1 from sysobjects where name='usp_list_columns') drop proc usp_list_columns " & _
        "begin " & _
        "declare @script as nvarchar(4000)" & _
        " set @script='" & _
        " create proc usp_list_columns(@table nvarchar(1000))" & _
        " as" & _
        " begin" & _
        " select o.name,c.colid,c.name Colonne,st.name Type,c.length/2 Longueur," & _
        " isnull(d.value,'''') description " & _
        " from syscolumns c inner join sysobjects o on c.id=o.id" & _
        " inner join systypes st on c.xtype=st.xtype" & _
        " left outer join " & _
        "(SELECT objtype, objname, name, value" & _
        " FROM ::fn_listextendedproperty (NULL, ''user'', ''dbo'', ''table'', @table, ''column'', default)" & _
        ")  d on c.name COLLATE Latin1_General_CI_AS=d.objname" & _
        " where o.name=@table and st.name <> ''sysname'' " & _
        " order by c.colid" & _
        " End '" & _
        " exec(@script)" & _
        " End"
        cConnetion.GetInstance.execute_script(strconnect, script)

        Dim p As SqlParameter
        Dim lp As New List(Of SqlParameter)

        p = New SqlParameter("@table", SqlDbType.NVarChar)
        p.Value = itable
        lp.Add(p)

        Return cConnetion.GetInstance.proc_to_table("usp_list_columns", lp, strconnect)
    End Function
    Public Shared Sub update_extendedproperties_columns(ByVal name As String, ByVal colonne As String, ByVal description As String, ByVal strconnect As String, ByVal colid As String, ByVal Type As String, ByVal Longueur As String)
        Dim script As String
        script = "if exists(select 1 from sysobjects where name='usp_update_extended_property_column') drop proc usp_update_extended_property_column;" & _
        " Begin" & _
        " Declare @script as nvarchar(4000);" & _
        " Set @script='" & _
        " CREATE proc usp_update_extended_property_column(@table nvarchar(1000),@column nvarchar(200),@desc sql_variant)  " & _
        " as  " & _
        " begin  " & _
        " if exists(select 1 from sysobjects where xtype=''u'' and name=@table)  " & _
        " begin  " & _
        " if exists(select 1 from ::fn_listextendedproperty(NULL,''user'',''dbo'',''table'',@table,N''column'',DEFAULT) where objname=@column)  " & _
        " EXECUTE sp_updateextendedproperty N''MS_Description'', @desc, N''user'', N''dbo'', N''table'', @table,  N''column'', @column  " & _
        " else  " & _
        " EXECUTE sp_addextendedproperty N''MS_Description'', @desc, N''user'', N''dbo'', N''table'', @table,  N''column'', @column   " & _
        " end end '" & _
         " exec(@script);" & _
        " End;"
        cConnetion.GetInstance.execute_script(strconnect, script)


        Dim p As SqlParameter
        Dim lp As New List(Of SqlParameter)

        p = New SqlParameter("@table", SqlDbType.NVarChar)
        p.Value = name
        lp.Add(p)

        p = New SqlParameter("@column", SqlDbType.NVarChar)
        p.Value = colonne
        lp.Add(p)

        p = New SqlParameter("@desc", SqlDbType.NVarChar)
        p.Value = description
        lp.Add(p)

        cConnetion.GetInstance.exec_proc("usp_update_extended_property_column", lp, strconnect)

    End Sub
    Public Shared Function get_sql_version(ByVal strconnect As String) As String
        Dim str As String
        str = "SELECT @@VERSION version"
        Dim dt As DataTable
        dt = cConnetion.GetInstance.script_to_table(strconnect, str)
        If dt IsNot Nothing AndAlso dt.Rows.Count > 0 Then
            Return dt.Rows(0).Item(0)
        Else
            Return "Not found"
        End If
    End Function
    Public Shared Function get_object_text(ByVal iobject As String, ByVal strconnect As String) As String
        Dim str As String
        str = "sp_helptext " & iobject
        Dim dt As DataTable
        dt = cConnetion.GetInstance.script_to_table(strconnect, str)
        If dt IsNot Nothing AndAlso dt.Rows.Count > 0 Then
            Dim otext As String = ""
            Dim i As Integer
            For i = 0 To dt.Rows.Count - 1
                otext = otext & dt.Rows(i).Item(0)
            Next
            Return otext
        Else
            Return "Not found"
        End If
    End Function
End Class


     

Further Readings:

Responses

No response found. Be the first to respond this post

Post Comment

You must Sign In To post reply
Here

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend