Member Avatar for jason_27

Hi please help me

VB.NET source program that could get a name of database SQL Server file from user and show all tables and fields and size and also SP , view

Please answer me
If you put the source code of the program, I am very grateful
Iam very much in need

  • 3 Contributors
  • 4 Replies
  • 354 Views
  • 20 Hours Discussion Span
  • Latest Post Latest Post by Dani

Dani AI

Generated

asked for a VB.NET tool to accept a SQL Server database (or an MDF file path) and list tables, columns, sizes, stored procedures and views. Split the work into small, testable steps: 1) attach or select the database; 2) write and test the SQL metadata queries in SSMS/sqlcmd (exactly as suggested); 3) call those queries from VB.NET via SqlClient and present the results. Below are compact, reliable queries and a minimal VB.NET pattern to run them.

SQL (run these first in SSMS to confirm results):

-- all columns
SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH
FROM INFORMATION_SCHEMA.COLUMNS
ORDER BY TABLE_SCHEMA, TABLE_NAME, ORDINAL_POSITION;
-- stored procedures
SELECT ROUTINE_SCHEMA, ROUTINE_NAME
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = 'PROCEDURE'
ORDER BY ROUTINE_SCHEMA, ROUTINE_NAME;
-- views
SELECT TABLE_SCHEMA, TABLE_NAME
FROM INFORMATION_SCHEMA.VIEWS
ORDER BY TABLE_SCHEMA, TABLE_NAME;
-- database files (size in MB)
SELECT name, physical_name, size * 8.0 / 1024 AS SizeMB
FROM sys.database_files;
-- approximate table sizes (MB)
SELECT SCHEMA_NAME(t.schema_id) AS SchemaName,
       t.name AS TableName,
       SUM(p.rows) AS RowCount,
       SUM(a.total_pages) * 8.0 / 1024 AS TotalMB,
       SUM(a.used_pages) * 8.0 / 1024 AS UsedMB
FROM sys.tables t
JOIN sys.indexes i ON t.object_id = i.object_id
JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
JOIN sys.allocation_units a ON p.partition_id = a.container_id
GROUP BY SCHEMA_NAME(t.schema_id), t.name
ORDER BY TotalMB DESC;

VB.NET pattern to run any of the above and load results into a DataTable:

Imports System.Data
Imports System.Data.SqlClient

Function ExecuteQuery(connStr As String, sql As String) As DataTable
    Dim dt As New DataTable()
    Using cn As New SqlConnection(connStr)
        Using cmd As New SqlCommand(sql, cn)
            cn.Open()
            Using rdr = cmd.ExecuteReader()
                dt.Load(rdr)
            End Using
        End Using
    End Using
    Return dt
End Function

Troubleshooting notes: if the user supplies an MDF file path, prefer attaching it to a known instance or use LocalDB with AttachDbFilename in the connection string; AttachDbFilename can fail if the file is already attached or permissions are insufficient. Some DMV/sys queries may require extra rights (VIEW DEFINITION / VIEW DATABASE STATE) to return full results. Validate any user-supplied name before building a connection string (use SqlConnectionStringBuilder) and test each SQL step in SSMS before wiring the UI.

Recommended Answers

All 4 Replies

Member Avatar for rproffitt

Duplicate at

Member Avatar for Dani

Yes but stack overflow refuses to help him because of their strict rules against discussions. So why can't we? It's not like he already got an amazing answer elsewhere.

For what it's worth, I apologize but I don't know VB.net, and we ask that you put some effort into your question with a description of where you're stuck and how we can help. We can't just write a whole database driven app for you. Plus you haven't provided any specifics.

Member Avatar for rproffitt

OK Dani,

Here goes. Let's say I have a MySQL server and my goal is to "show all tables and fields and size". My first step has nothing to do with VB at all. What is my first step?

-> I run my SQL command line tool and create the command needed to show all tables on the SQL server. That is then written down for later use in my VB app. Next I craft my SQL command to the table structure then size and note that for my VB app.

Now I'm almost ready to start my VB app work. I'm running into folk that won't work on their apps in a stepwise method. Hopefull this will get them going.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.