• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 393
  • Last Modified:

VS2010 - Data Access for a Project

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???

3 Solutions
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
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.  :)
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.


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.
Amour22015Author Commented:
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

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

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

A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

Anthony PerkinsCommented:
I suggest you look at something like Red Gate's SQL Doc.  This tool will document all your schema and code.
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 @t_name VARCHAR(260)    
DECLARE @c_name VARCHAR(128)
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


        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]
                  ,CASE  WHEN c.[is_nullable] = 1 THEN  'NULL' ELSE 'NOT NULL' END AS  [Nullable]
          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]

OPEN UserTbls
        FETCH NEXT  FROM UserTbls
                  INTO @t_name

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

                                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


CLOSE UserTbls

Open in new window

Amour22015Author Commented:
Thanks to all...
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now