Solved

VS2010 - Data Access for a Project

Posted on 2014-04-07
6
384 Views
Last Modified: 2016-02-10
Hi and thanks,

I am very new to all this.

I would like to:

Determine what databases/tables are being accessed/updated within a project.

Apparently there is no documentation yet on the project(s).

I have not been assigned anything yet, but would like to be a little propared for the near future when I do.

I just am looking for some direction at this point???

Thanks
0
Comment
Question by:Amour22015
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 22

Assisted Solution

by:plusone3055
plusone3055 earned 70 total points
ID: 39984041
well the best way ot determine this is to look within the source code of the project/ application.

unless there is documentation on the application/project.  it could prove difficult.
of course you can always take guesses and then look at the databases and tables.

for instance
if you have an application that has 4 textboxews

first name
last name
DOB
phone number

you can always enter some data in and them run a search string query against the databases with the data you entered to determine that tables/columns  the data went into.  :)
0
 
LVL 6

Assisted Solution

by:Dulton
Dulton earned 180 total points
ID: 39984111
SQL Profiler may give you some insight in real-time. It isn't going to be a blueprint, but it can show you what is happening at that instant. (which means if you are curious, you can setup the profiler, execute the task/command you're questioning and then review the profiler trace to see what SQL did.

http://msdn.microsoft.com/en-us/library/ff650699.aspx

There are also some 3rd party tools which will document/diagram the dbs for you. I have no personal experience, but maybe others do! I won't promote any one over another since I've never used one.
0
 

Author Comment

by:Amour22015
ID: 39984395
Looks like I might have to do this the hard way and get all the table information within a database.


So far I have this:
USE Your Database Name
SELECT name FROM sys.tables
ORDER BY name

This works to bring up a list of Tables in a DB

BUT:
How can I get this to either get copied to word or printed???

Thanks
0
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39985783
I suggest you look at something like Red Gate's SQL Doc.  This tool will document all your schema and code.
0
 
LVL 6

Accepted Solution

by:
Dulton earned 180 total points
ID: 39985811
If you're using Sql Server Management Studio, you can right click in the top-left of the output grid and either copy/paste into excel or word (I'd recommend excel for this) or rather choose "save as" and export the results as a tab delimited text file.

if you must have a text output, try this code here. if you toggle the parameter @OrderByScheama, you can change whether the schema begins the table name or is rather included in () at the end of the table name.

DECLARE @objId INT
DECLARE @t_name VARCHAR(260)    
DECLARE @c_name VARCHAR(128)
DECLARE @c_pos INT
DECLARE @d_type VARCHAR(128)
DECLARE @max_length INT
DECLARE @precision INT
DECLARE @nullable VARCHAR(8)
DECLARE @k_type VARCHAR(128)
DECLARE @OrderBySchema BIT = 1

 
DECLARE UserTbls CURSOR  FOR

        SELECT CASE  @OrderBySchema WHEN 1  THEN s.[name] + '.'  + t.[name]
                                                           ELSE t.[name] + ' ('  + s.[name] + ')'  END AS [t_name]
              ,c.[name] AS [c_name]
                  ,c.[column_id] AS [c_position]
                  ,t1.[name]
                  ,c.[max_length]
                  ,c.[precision]
                  ,CASE  WHEN c.[is_nullable] = 1 THEN  'NULL' ELSE 'NOT NULL' END AS  [Nullable]
                  ,k.[type_desc]
          FROM sys.[all_columns] AS c
INNER JOIN sys.[tables] AS t
                ON c.[object_id] = t.[object_id]
INNER JOIN sys.[schemas] AS s
            ON t.[schema_id] = s.[schema_id]
INNER JOIN sys.[types] as t1
                ON c.[user_type_id] = t1.[user_type_id]
LEFT JOIN [sys].[key_constraints] AS k
                ON t.[object_id] = k.[parent_object_id]
           AND c.[column_id] = k.[unique_index_id]
        WHERE t.[type] = 'U'            --user tables only
  ORDER BY  [t_name]
                  ,[c_position]

 
OPEN UserTbls
        FETCH NEXT  FROM UserTbls
                  INTO @t_name
                          ,@c_name
                          ,@c_pos
                          ,@d_type
                          ,@max_length
                          ,@precision
                          ,@nullable
                          ,@k_type

                WHILE @@FETCH_STATUS = 0
                        BEGIN
                        IF @c_pos  = 1
                                BEGIN
                                        PRINT ''
                                        PRINT @t_name  + Replicate(' ',10) + '(MaxLength,Precision)'
                                        PRINT Replicate('-',50)         
                                END                     

                                PRINT Replicate(' ',4) + @c_name  + ' ' 
                                                                           + @d_type +  '(' 
                                                                           + Cast(@max_length AS VARCHAR(10)) + ','
                                                                           + Cast(@precision AS VARCHAR(10)) + ') '
                                                                           + @nullable
                                                                           + Isnull(' ' + @k_type,'') 

                        FETCH NEXT  FROM UserTbls 
                                INTO @t_name
                                    ,@c_name
                                    ,@c_pos
                                    ,@d_type
                                    ,@max_length
                                    ,@precision
                                    ,@nullable
                                        ,@k_type

                        END

CLOSE UserTbls
DEALLOCATE userTbls

Open in new window

0
 

Author Closing Comment

by:Amour22015
ID: 39985876
Thanks to all...
0

Featured Post

Do you have a plan for Continuity?

It's inevitable. People leave organizations creating a gap in your service. That's where Percona comes in.

See how Pepper.com relies on Percona to:
-Manage their database
-Guarantee data safety and protection
-Provide database expertise that is available for any situation

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
Your data is at risk. Probably more today that at any other time in history. There are simply more people with more access to the Web with bad intentions.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

630 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question