February 25, 2020 at 8:39 pm
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
February 25, 2020 at 8:54 pm
Go ahead and run it. You are selecting from system tables, not data tables. This should cause no issues.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
February 25, 2020 at 9:08 pm
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) A socialist is someone who will give you the shirt off *someone else's* back.
February 25, 2020 at 9:32 pm
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
February 25, 2020 at 9:36 pm
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) A socialist is someone who will give you the shirt off *someone else's* back.
February 25, 2020 at 10:12 pm
Thank you all for your suggestions, it helps!
February 26, 2020 at 4:11 am
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
Change is inevitable... Change for the better is not.
February 26, 2020 at 5:15 pm
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) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply