Running generic query for table information

  • sizal0234

    SSCrazy

    Points: 2080

    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

     

  • Phil Parkin

    SSC Guru

    Points: 244107

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

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

  • ScottPletcher

    SSC Guru

    Points: 98398

    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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • Jonathan AC Roberts

    SSCoach

    Points: 17204

    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: 98398

    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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • sizal0234

    SSCrazy

    Points: 2080

    Thank you all for your suggestions, it helps!

  • Jeff Moden

    SSC Guru

    Points: 995976

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "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: 98398

    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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

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

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