SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Calculate tables in a schema


Calculate tables in a schema

Author
Message
sharonsql2013
sharonsql2013
Mr or Mrs. 500
Mr or Mrs. 500 (598 reputation)Mr or Mrs. 500 (598 reputation)Mr or Mrs. 500 (598 reputation)Mr or Mrs. 500 (598 reputation)Mr or Mrs. 500 (598 reputation)Mr or Mrs. 500 (598 reputation)Mr or Mrs. 500 (598 reputation)Mr or Mrs. 500 (598 reputation)

Group: General Forum Members
Points: 598 Visits: 1265
I need to calculate total # of tables in a particular schema.
Its a user defined table.(Total number of tables seem a lot)


I can view the schema id from
select * from sys.schemas.


Kindly advise.
Sean Pearce
Sean Pearce
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1786 Visits: 3432
SELECT schema_id, name FROM sys.tables



The SQL Guy @ blogspot

@SeanPearceSQL

About Me
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26323 Visits: 17553
Of if you want to use the schema name.


select *
from sys.tables t
join sys.schemas s on t.schema_id = s.schema_id
where s.name = 'YourSchemaName'



_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
sharonsql2013
sharonsql2013
Mr or Mrs. 500
Mr or Mrs. 500 (598 reputation)Mr or Mrs. 500 (598 reputation)Mr or Mrs. 500 (598 reputation)Mr or Mrs. 500 (598 reputation)Mr or Mrs. 500 (598 reputation)Mr or Mrs. 500 (598 reputation)Mr or Mrs. 500 (598 reputation)Mr or Mrs. 500 (598 reputation)

Group: General Forum Members
Points: 598 Visits: 1265
That sure helps.

Thank you.
Steven Willis
Steven Willis
SSC Eights!
SSC Eights! (843 reputation)SSC Eights! (843 reputation)SSC Eights! (843 reputation)SSC Eights! (843 reputation)SSC Eights! (843 reputation)SSC Eights! (843 reputation)SSC Eights! (843 reputation)SSC Eights! (843 reputation)

Group: General Forum Members
Points: 843 Visits: 1721
Also available under System Views is the INFORMATION_SCHEMA collection which can give you all the information you need for any metadata in the the database.

For listing out your table collection for example:



SELECT * FROM
INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'




This will list every table in the target database.

 
dwain.c
dwain.c
SSCertifiable
SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)

Group: General Forum Members
Points: 7399 Visits: 6431
Steven Willis (7/15/2013)
Also available under System Views is the INFORMATION_SCHEMA collection which can give you all the information you need for any metadata in the the database.

For listing out your table collection for example:



SELECT * FROM
INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'




This will list every table in the target database.

 


Steven - I believe I've heard somewhere that INFORMATION_SCHEMA VIEWs will be deprecated in the future. Too bad because I've found them pretty useful.


My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
Steven Willis
Steven Willis
SSC Eights!
SSC Eights! (843 reputation)SSC Eights! (843 reputation)SSC Eights! (843 reputation)SSC Eights! (843 reputation)SSC Eights! (843 reputation)SSC Eights! (843 reputation)SSC Eights! (843 reputation)SSC Eights! (843 reputation)

Group: General Forum Members
Points: 843 Visits: 1721
I've heard rumors that the INFORMATION_SCHEMA views are deprecated in later versions, but the information even on MSDN isn't very clear on that. http://msdn.microsoft.com/en-us/library/ms186778.aspx. It seems the INFORMATION_SCHEMA views are still the ANSI-compliant method for querying metadata (if that matters). The link posted does say that some of the views have changed seemingly to be MORE ANSI-compliant than before. From the description at that link it seems mostly the changes involve mandating strict naming conventions. I hope these views don't go away entirely since they have proven very useful to me!

 
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search