Running generic query for table information

Question

Running generic query for table information

sizal0234

SSCrazy

Points: 2272
More actions
February 25, 2020 at 8:39 pm

#3728725

Hi,
I want to run the below query to get information about the tables and columns for better understanding. However my fear is since the databases are in Tb's and has many tables will this query cause any intermittent slowness or any blocking since the system is busy when I am running this query. What is the best practice to run something like below.
SELECT T.Name                   AS TableName,
Schema_name(T.schema_id) AS SchemaName,
C.Name                   AS ColumnName,
Ty.Name                  AS ColumnDataType,
C.is_nullable            AS IsNullAble,
C.is_identity            AS IsIdentity
FROM   sys.tables T
INNER JOIN sys.columns C
ON T.OBJECT_ID = C.OBJECT_ID
INNER JOIN sys.types Ty
ON C.system_type_id = Ty.system_type_id
WHERE  T.is_ms_shipped = 0
ORDER  BY T.name

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

Go ahead and run it. You are selecting from system tables, not data tables. This should cause no issues.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

ScottPletcher SSC Guru Points: 101098 More actions · Answer 2

You can use "WITH (NOLOCK)" on system views, and I suggest you do so here.

Your join on the types table is incorrect, you should use user_type_id not system_type_id.

If you're worried about querying this live, you could capture this info nightly and use that for most queries, including today's mods only if necessary.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Jonathan AC Roberts SSCoach Points: 18354 More actions · Answer 3

An alternative query for the same results:

select c.TABLE_NAME,
       c.TABLE_SCHEMA,
       c.COLUMN_NAME,
       c.DATA_TYPE,
       c.IS_NULLABLE,
       COLUMNPROPERTY(object_id(quotename(c.TABLE_SCHEMA)+'.'+ quotename(c.TABLE_NAME)),c.COLUMN_NAME,'IsIdentity') IsIdentity
  from INFORMATION_SCHEMA.columns c
 order by c.TABLE_NAME, c.ORDINAL_POSITION

ScottPletcher SSC Guru Points: 101098 More actions · Answer 4

I'd avoid INFORMATION_SCHEMA views completely. I've found them to be slow (I understand that the view definition doesn't look like it should be slow, but real-world use says otherwise; if true, I doubt MS would ever confirm it, as they wouldn't want to be seen as disparaging the ISO-standard views).

They also don't contain all the attributes available in sys views, and can be inaccurate.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

sizal0234 SSCrazy Points: 2272 More actions · Answer 5

sizal0234

SSCrazy

Points: 2272

February 25, 2020 at 10:12 pm

#3728750

Thank you all for your suggestions, it helps!

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 6

Instead of using WITH (NOLOCK) on every system table, use SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED for the session. That way you don't have to worry about missing one and, if you ever need to turn it off, you only have one line of code to find and comment out.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ScottPletcher SSC Guru Points: 101098 More actions · Answer 7

Jeff Moden wrote:

Instead of using WITH (NOLOCK) on every system table, use SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED for the session. That way you don't have to worry about missing one and, if you ever need to turn it off, you only have one line of code to find and comment out.

That would, of course, apply it to every table, not just the system views, so be careful with that. If, for example, you're doing UPDATEs based on SELECTs somewhere else in the code, those SELECTs would also be affected by that blanket SET.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".