FROM [thewebsitedb].[dbo].[sysobjects] tab,
[thewebsitedb].[dbo].[syscolumns] col LEFT OUTER JOIN
[thewebsitedb].[dbo].[syscomments] com INNER JOIN
[thewebsitedb].[dbo].[sysobjects] obj ON com.id = obj.id ON col.cdefault = com.id AND com.colid = 1,
[thewebsitedb].[dbo].[systypes] typ
WHERE tab.id = col.id
AND tab.xtype = 'U'
AND tab.name <> 'dtproperties'
AND col.xusertype = typ.xusertype
ELSE
SELECT
tab.name as 'table',
col.name as 'column',
typ.name as 'column_type',
typ.length as 'column_size'
FROM [thewebsitedb].[dbo].[sysobjects] tab,
[thewebsitedb].[dbo].[syscolumns] col LEFT OUTER JOIN
[thewebsitedb].[dbo].[syscomments] com INNER JOIN
[thewebsitedb].[dbo].[sysobjects] obj ON com.id = obj.id ON col.cdefault = com.id AND com.colid = 1,
[thewebsitedb].[dbo].[systypes] typ
WHERE tab.id = col.id
AND tab.name = @InputTable
AND tab.xtype = 'U'
AND tab.name <> 'dtproperties'
AND col.xusertype = typ.xusertype
ORDER BY col.colid
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Option Explicit On
Option Strict On
Option Compare Text
Imports System
Imports System.Configuration
Imports System.Data
Imports System.Data.SqlClient
Namespace SQLSERVER
Public Class ConnectionTest : Inherits System.Web.UI.Page
'-----DECLARING connection string value ---
Public Const strConnectionInfo As String = "Data Source=localhost;Database=MyDB;Integrated Security=SSPI"
'--- to avoid direct exposure of your conncetion string - change it to something like:
'Public Const strConnectionInfo As String = ConfigurationSettings.AppSettings.Get("ConnectionString")
'-----DECLARING default values
Public Const intConnectionTimeout As Short = 10
Public Const intCommandTimeout As Short = 60
Public Const strProjectName As String = "THEDBData"
Public Const DefaultValue_String As String = "**** NULL VALUE ****"
Public Const DefaultValue_Double As Double = -2147483648.0#
Public Const DefaultValue_Single As Single = -2147483648.0#
Public Const DefaultValue_Date As Date = #1/1/1753#
Public Const DefaultValue_Integer As Integer = -2147483648
Public Const DefaultValue_Short As Short = -32768
Public Const DefaultValue_Byte As Byte = 0
Public Const DefaultValue_Currency As Decimal = -2147483648
Public Const DefaultValue_TimeStamp As String = "FFFFFFFF"
Public Const DefaultValue_Boolean As Short = -32768
Protected ddlTable As System.Web.UI.WebControls.DropDownList
Protected txtTable As System.Web.UI.WebControls.TextBox
Protected dtgTables As System.Web.UI.WebControls.DataGrid
Dim objConn As SqlConnection
Dim objCmd As SqlCommand
Dim dataReader As SqlDataReader
Dim strSql As String
Private Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles MyBase.Load
If Not IsPostBack Then
Dim objConn As SqlConnection
Dim dataReader As SqlDataReader
objConn = New SqlConnection(strConnectionInfo)
strSql = "EXECUTE [thewebsitedb].[dbo].[selectTableColumnsNamesTypesSizes]"
objCmd = New SqlCommand(strSql, objConn)
Try
objConn.Open()
dataReader = objCmd.ExecuteReader
With ddlTable
.DataSource = dataReader
.DataTextField = "table"
.DataValueField = "table"
.DataBind()
End With
Catch
Finally
objConn.Close()
objConn.Dispose()
End Try
End If
End Sub
Public Sub btnGetTable_Click(sender as Object, e As EventArgs)
objConn = New SqlConnection(strConnectionInfo)
if txtTable.Text <> "" then
strSQL = "EXECUTE [thewebsitedb].[dbo].[selectTableColumnsNamesTypesSizes] '" & txtTable.Text & "','',''"
else
strSQL = "EXECUTE [thewebsitedb].[dbo].[selectTableColumnsNamesTypesSizes] '" & ddlTable.SelectedItem.Text & "','',''"
end if
objCmd = New SqlCommand(strSQL, objConn)
Try
objConn.Open()
dataReader = objCmd.ExecuteReader()
'Bind to DataGrid
dtgTables.DataSource = dataReader
dtgTables.DataBind()
Catch
Finally
objConn.Close()
objConn.Dispose()
End Try
End Sub
None
0 Points
1 Post
How to describe user tables MS SQL Server in ASP.NET and stored procedure?
Sep 02, 2005 05:15 PM|NataliaZ|LINK
----------------------Stored procedure--------------------------------------------
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
ALTER PROCEDURE [selectTableColumnsNamesTypesSizes]
(
@InputTable varchar(200) = NULL,
@ColumnName varchar(200) = NULL output,
@ColumnType varchar(15) = NULL output,
@ColumnSize int=NULL output
)
AS
IF @InputTable IS NULL
SELECT distinct
tab.name as 'table'
FROM [thewebsitedb].[dbo].[sysobjects] tab,
[thewebsitedb].[dbo].[syscolumns] col LEFT OUTER JOIN
[thewebsitedb].[dbo].[syscomments] com INNER JOIN
[thewebsitedb].[dbo].[sysobjects] obj ON com.id = obj.id ON col.cdefault = com.id AND com.colid = 1,
[thewebsitedb].[dbo].[systypes] typ
WHERE tab.id = col.id
AND tab.xtype = 'U'
AND tab.name <> 'dtproperties'
AND col.xusertype = typ.xusertype
ELSE
SELECT
tab.name as 'table',
col.name as 'column',
typ.name as 'column_type',
typ.length as 'column_size'
FROM [thewebsitedb].[dbo].[sysobjects] tab,
[thewebsitedb].[dbo].[syscolumns] col LEFT OUTER JOIN
[thewebsitedb].[dbo].[syscomments] com INNER JOIN
[thewebsitedb].[dbo].[sysobjects] obj ON com.id = obj.id ON col.cdefault = com.id AND com.colid = 1,
[thewebsitedb].[dbo].[systypes] typ
WHERE tab.id = col.id
AND tab.name = @InputTable
AND tab.xtype = 'U'
AND tab.name <> 'dtproperties'
AND col.xusertype = typ.xusertype
ORDER BY col.colid
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
-------------------------------------ASPX.VB - code behind---------------------------------------------------
Option Explicit On
Option Strict On
Option Compare Text
Imports System
Imports System.Configuration
Imports System.Data
Imports System.Data.SqlClient
Namespace SQLSERVER
Public Class ConnectionTest : Inherits System.Web.UI.Page
'-----DECLARING connection string value ---
Public Const strConnectionInfo As String = "Data Source=localhost;Database=MyDB;Integrated Security=SSPI"
'--- to avoid direct exposure of your conncetion string - change it to something like:
'Public Const strConnectionInfo As String = ConfigurationSettings.AppSettings.Get("ConnectionString")
'-----DECLARING default values
Public Const intConnectionTimeout As Short = 10
Public Const intCommandTimeout As Short = 60
Public Const strProjectName As String = "THEDBData"
Public Const DefaultValue_String As String = "**** NULL VALUE ****"
Public Const DefaultValue_Double As Double = -2147483648.0#
Public Const DefaultValue_Single As Single = -2147483648.0#
Public Const DefaultValue_Date As Date = #1/1/1753#
Public Const DefaultValue_Integer As Integer = -2147483648
Public Const DefaultValue_Short As Short = -32768
Public Const DefaultValue_Byte As Byte = 0
Public Const DefaultValue_Currency As Decimal = -2147483648
Public Const DefaultValue_TimeStamp As String = "FFFFFFFF"
Public Const DefaultValue_Boolean As Short = -32768
Protected ddlTable As System.Web.UI.WebControls.DropDownList
Protected txtTable As System.Web.UI.WebControls.TextBox
Protected dtgTables As System.Web.UI.WebControls.DataGrid
Dim objConn As SqlConnection
Dim objCmd As SqlCommand
Dim dataReader As SqlDataReader
Dim strSql As String
Private Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles MyBase.Load
If Not IsPostBack Then
Dim objConn As SqlConnection
Dim dataReader As SqlDataReader
objConn = New SqlConnection(strConnectionInfo)
strSql = "EXECUTE [thewebsitedb].[dbo].[selectTableColumnsNamesTypesSizes]"
objCmd = New SqlCommand(strSql, objConn)
Try
objConn.Open()
dataReader = objCmd.ExecuteReader
With ddlTable
.DataSource = dataReader
.DataTextField = "table"
.DataValueField = "table"
.DataBind()
End With
Catch
Finally
objConn.Close()
objConn.Dispose()
End Try
End If
End Sub
Public Sub btnGetTable_Click(sender as Object, e As EventArgs)
objConn = New SqlConnection(strConnectionInfo)
if txtTable.Text <> "" then
strSQL = "EXECUTE [thewebsitedb].[dbo].[selectTableColumnsNamesTypesSizes] '" & txtTable.Text & "','',''"
else
strSQL = "EXECUTE [thewebsitedb].[dbo].[selectTableColumnsNamesTypesSizes] '" & ddlTable.SelectedItem.Text & "','',''"
end if
objCmd = New SqlCommand(strSQL, objConn)
Try
objConn.Open()
dataReader = objCmd.ExecuteReader()
'Bind to DataGrid
dtgTables.DataSource = dataReader
dtgTables.DataBind()
Catch
Finally
objConn.Close()
objConn.Dispose()
End Try
End Sub
End Class
End Namespace
---------------------------------------------------ASPX---------------------------------------------------------
<%@ Page Language="vb" Src="DescribeDatabase.aspx.vb" Inherits="SQLSERVER.DescribeDatabase" AutoEventWireup="false"%>
<% 'Next 3 lines prevent caching in IIS. IE caching is handled by meta tags %>
<% Response.CacheControl = "no-cache" %>
<% Response.AddHeader("Pragma", "no-cache")%>
<% Response.Expires = -1 %>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html>
<head>
<title>DocumentAdmin</title>
<meta content="True" name="vs_showGrid">
<meta content="Microsoft Visual Studio.NET 7.0" name="GENERATOR">
<meta content="Visual Basic 7.0" name="CODE_LANGUAGE">
<meta content="JavaScript" name="vs_defaultClientScript">
<meta content="http://schemas.microsoft.com/intellisense/ie5" name="vs_targetSchema">
<LINK href="styles.css" type="text/css" rel="stylesheet">
</head>
<body MS_POSITIONING="GridLayout" style="margin: 30 30 30 30;" oncontextmenu="return false;">
<form id="Form1" method="post" runat="server" enctype="multipart/form-data">
<asp:Label id="lblSelect" text="Select a Table: " runat="server" />
<asp:DropDownList id="ddlTable" runat="server" />
or type table name:
<asp:TextBox id="txtTable" runat="server" />
<asp:Button id="btnGetTable" text="Describe Table" OnClick="btnGetTable_Click" runat="server" />
<br>
<asp:DataGrid ID="dtgTables" runat="server"
AutoGenerateColumns=True
HeaderStyle-BackColor="IndianRed"
HeaderStyle-Font-Bold="True"
HeaderStyle-Font-Name="Verdana"
HeaderStyle-Font-Size="12px"
HeaderStyle-ForeColor="White"
ItemStyle-BackColor=Gainsboro
ItemStyle-Font-Name="verdana"
ItemStyle-Font-Size="12px"
Width="75%">
</asp:DataGrid>
</form>
</body>
</html>