May 4, 2011 at 8:45 am
I just started a new job working SQL Server databases and I am trying to figure out the best way to find data from various tables to write queries against.
May 4, 2011 at 8:51 am
Are you asking how to write a SELECT statement?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 4, 2011 at 9:06 am
If there is an application, or a specific set of data you are looking to find, here's what I'd suggest. Make note of some values that you want to query on.
Next, work your way through the list of tables, perhaps from SSMS and the Tables folder or from the result of this query
select table_name from information_schema.tables
For each table you get, I'd run a
select top 100
*
from
and see what data is returned. Make some notes as best you can on what each table stores and what the columns mean. Once you know what data is stored in each table, you can query for more information or join tables together.
May 4, 2011 at 9:09 am
I apologize for not being clear. I know how the write SELECT statements, my problem is that I don't know how the database was designed so when I receive a request for information , I find it difficult to determine what table to retrieve the information from. The database has over 300 tables and I know there has to be a better way to figure out which tables contain the information I need instead of running a SELECT statement for every single table.
May 4, 2011 at 9:23 am
Is there an existing application or reporting tool that already is writing or retrieving data against the data?
If so you can run the SQL Server Profiler tool to see what tables are being used by the application/reporting tools.
May 4, 2011 at 9:37 am
There are a few existing applications but the only problem is that I am using SQL Server Management Studio Express to connect to the database and don't think SSMSE have the Profiler tool.
May 4, 2011 at 9:45 am
This script came from one of the many blogs that I read - sorry but I can't remember which one right now.
WITH doc_db AS (
SELECT Object_schema_name(objectid) AS object_schema,
Object_name(objectid) AS object_name, parameter,
typename + ' ' + CASE WHEN typename IN ('char', 'varchar', 'nchar', 'nvarchar')
THEN '(' + CASE WHEN length = -1 THEN 'MAX'
ELSE CONVERT(VARCHAR(4), CASE WHEN TypeName IN ('nchar', 'nvarchar')
THEN length / 2
ELSE length
END)
END + ')'
WHEN typename IN ('decimal', 'numeric')
THEN '(' + CONVERT(VARCHAR(4), precision) + ',' + CONVERT(VARCHAR(4), scale) + ')'
ELSE ''
END + CASE WHEN XML_collection_ID <> 0
THEN '(' + CASE WHEN is_XML_Document = 1
THEN 'DOCUMENT '
ELSE 'CONTENT '
END + COALESCE((SELECT TOP 1 QUOTENAME(ss.name) + '.' + QUOTENAME(sc.Name)
FROM sys.xml_schema_collections sc
INNER JOIN Sys.Schemas ss ON sc.schema_ID = ss.schema_ID
WHERE sc.xml_collection_ID = XML_collection_ID),'NULL') + ')'
ELSE ''
END AS datatype,
dataobjecttype, NULLIF(collation_name, 'parameter') AS collation_name
FROM (SELECT t.name AS typename, REPLACE(c.name, '@', '') AS parameter,
c.max_length AS length, c.precision AS precision,
c.scale AS scale, c.object_id AS objectid,
xml_collection_id, is_xml_document, 'P' AS dataobjecttype,
'parameter' AS collation_name
FROM sys.parameters c
INNER JOIN sys.types t ON c.user_type_id = t.user_type_id AND parameter_id > 0
UNION all
SELECT t.name AS typename, c.name AS parameter, c.max_length AS length,
c.precision AS precision, c.scale AS scale,
c.object_id AS objectid, xml_collection_id, is_xml_document,
'C' AS dataobjecttype, ISNULL(c.collation_name,'None') AS collation_name
FROM sys.columns c
INNER JOIN sys.types t ON c.user_Type_ID = t.user_Type_ID
WHERE OBJECT_SCHEMA_NAME(c.object_ID) <> 'sys'
) f
)
SELECT object_schema, object_name, CASE WHEN dataobjecttype ='P'
THEN '@'
ELSE ''
END + parameter AS field,
datatype, collation_name
FROM doc_db
--WHERE parameter IN (SELECT parameter
-- FROM doc_db
-- GROUP BY parameter
-- HAVING MIN(datatype)<>MAX(datatype))
--ORDER BY parameter
ORDER BY object_name
Here's another one (I use this one to keep a data-dictionary on all of our tables. We fill in the meta descritpion, which allows us to keep track of data) : -
SELECT
Object_schema_name(c.object_id) AS object_schema, Object_name(c.object_id) AS object_name,
c.name AS field, CASE WHEN t.name <> 'uniqueidentifier'
THEN t.name
ELSE 'GUID' END AS datatype,
CASE WHEN t.name IN ('char', 'varchar', 'nchar', 'nvarchar')
THEN CASE WHEN c.max_length = -1
THEN 'MAX'
ELSE CONVERT(VARCHAR(4), CASE WHEN t.name IN ('nchar', 'nvarchar')
THEN c.max_length / 2
ELSE c.max_length END) END
WHEN t.name IN ('decimal', 'numeric')
THEN CONVERT(VARCHAR(4), c.precision) + ',' + CONVERT(VARCHAR(4), c.scale)
ELSE '' END AS datasize, cd.value AS meta_description_column
FROM sys.columns c
INNER JOIN sys.types t ON c.user_type_id = t.user_type_id
LEFT OUTER JOIN sys.extended_properties cd ON cd.minor_id = c.object_id AND cd.name = 'MS_Description'
WHERE Object_schema_name(c.object_id) <> 'sys'
GROUP BY Object_schema_name(c.object_id), Object_name(c.object_id), c.name, t.name, c.max_length,
c.scale, c.precision, cd.value
ORDER BY Object_schema_name(c.object_id)
May 4, 2011 at 10:04 am
skcadavre, thanks for the scripts, but when I run them, I get the following errors:
1st script:
Msg 195, Level 15, State 10, Line 3
'Object_schema_name' is not a recognized built-in function name.
Msg 102, Level 15, State 1, Line 22
Incorrect syntax near ','.
Msg 195, Level 15, State 10, Line 40
'OBJECT_SCHEMA_NAME' is not a recognized built-in function name.
2nd script:
Msg 195, Level 15, State 10, Line 2
'Object_schema_name' is not a recognized built-in function name.
May 4, 2011 at 10:08 am
Are you running SQL Server 2000? I assumed you're running 2008 since that is the forum you posted in.
May 4, 2011 at 10:16 am
There isn't a good way to figure out what's in tables. If I create a C0101 table, SQL Server has no idea what I store in there. You're depending on someone using good column names or table names.
My suggestion was there to help you start building that data dictionary. You can use the queries above, but ultimately you will have to annotate the results to understand what data is being stored.
May 4, 2011 at 10:20 am
Steve Jones - SSC Editor (5/4/2011)
There isn't a good way to figure out what's in tables. If I create a C0101 table, SQL Server has no idea what I store in there. You're depending on someone using good column names or table names.My suggestion was there to help you start building that data dictionary. You can use the queries above, but ultimately you will have to annotate the results to understand what data is being stored.
Most definitely agreed, which is why we fill in a description of the data in the meta columns for the tables. I can then use the second query I posted to find any NULL meta_description columns and slap whoever was working on that area (we have a small IT dept, so it's easy to find out :w00t:)
May 4, 2011 at 11:07 am
No, I am running SQL Server 2008.
May 4, 2011 at 11:17 am
troe_atl (5/4/2011)
No, I am running SQL Server 2008.
Case sensitive server?
Compatibility level maybe?
May 4, 2011 at 11:32 am
Even without Profiler, you can still create/use a trace to get what queries are being run in the database. You just have to script it out yourself. Check out the documentation on sp_trace_create, sp_trace_setfilter, fn_trace_getinfo, and fn_trace_gettable. You can find specifics on them in BOL or on MSDN, whichever you prefer (I use MSDN these days).
The advantage to that is, if you trace batch-complete, you'll see actual queries, and you can probably re-run them in SSMS, so you can see what they're doing. Of course, do that on a dev copy of the database if they are anything beyond Select statements.
I've used that method many times to begin documenting a "mystery database". Tremendously helpful.
If the tables and columns have meaningful names, you can usually just take a look at sys.columns, joined to sys.tables, and you'll find out a lot about the database that way. If it has obscured or, worse, poorly chosen names (I'm tempted to call some of what I've seen "completely idiotic names", but "poorly chosen" sounds more diplomatic), then traces will get you further faster than just about anything else. Even with meaningful names, a trace will get you a lot of what things are being used for, which is more important than what they're called anyway.
I have a database where the prior DBA created a table with a column "SecondAccountNumber", which has datetime data in it. Nobody here now knows what it was for, and it's not currently in any use we know of. Another table has a "FaxNumber" column, in which he stored response-codes from marketing campaigns. Yet another has a table with "Column01", "Column02", and so on through "Column25". I was actually able to reverse engineer what each of those is being used for, but it took some work. Traces and code analysis were what was needed in order to track these things down. The traces told me which stored procs were in use, and what values were being assigned to each parameter, and then reading through the code told the rest of the story.
So, don't trust the column names till you've verified them.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 5, 2011 at 9:17 am
Thank you all for your help. I found that the tracing approach is helping me figure out what tables and applications are involved in the database transactions. I wasn't able to figure out how to script a trace, but I did find this cool profiler tool that I was able to use with SQL Server 2008 Express.
The link to the tool is below:
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply