|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
how to write a query to get list of user-defined typesI wanted to write a select query that returns a list of user-defined types
only (without returning the system types). In other words, I can't just do "select * from systypes". Does anyone know if this can be done? Check out sp_help in the BOL.
-- Tom ---------------------------------------------------- Thomas A. Moreau, BSc, PhD, MCSE, MCDBA SQL Server MVP Columnist, SQL Server Professional Toronto, ON Canada www.pinpub.com .. <arch> wrote in message news:42eb5cf6$1@funnel.arach.net.au... I wanted to write a select query that returns a list of user-defined typesonly (without returning the system types). In other words, I can't just do "select * from systypes". Does anyone know if this can be done? Thanks for the assistance, but sp_help returns a whole bunch of things. How
can I return ONLY a list of user-defined types? Show quote "Tom Moreau" <tom@dont.spam.me.cips.ca> wrote in message news:OqbJRdPlFHA.576@tk2msftngp13.phx.gbl... > Check out sp_help in the BOL. > > -- > Tom > > ---------------------------------------------------- > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA > SQL Server MVP > Columnist, SQL Server Professional > Toronto, ON Canada > www.pinpub.com > . > <arch> wrote in message news:42eb5cf6$1@funnel.arach.net.au... > I wanted to write a select query that returns a list of user-defined types > only (without returning the system types). In other words, I can't just > do > "select * from systypes". Does anyone know if this can be done? > > > > hi
probably this can help u select * from systypes where xusertype > 256 order by name please let me knowif u have any questions -- Show quotebest Regards, Chandra http://chanduas.blogspot.com/ http://groups.msn.com/SQLResource/ --------------------------------------- "arch" wrote: > Thanks for the assistance, but sp_help returns a whole bunch of things. How > can I return ONLY a list of user-defined types? > > "Tom Moreau" <tom@dont.spam.me.cips.ca> wrote in message > news:OqbJRdPlFHA.576@tk2msftngp13.phx.gbl... > > Check out sp_help in the BOL. > > > > -- > > Tom > > > > ---------------------------------------------------- > > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA > > SQL Server MVP > > Columnist, SQL Server Professional > > Toronto, ON Canada > > www.pinpub.com > > . > > <arch> wrote in message news:42eb5cf6$1@funnel.arach.net.au... > > I wanted to write a select query that returns a list of user-defined types > > only (without returning the system types). In other words, I can't just > > do > > "select * from systypes". Does anyone know if this can be done? > > > > > > > > > > > Thanks. That did the trick. If I can ask one more question: What SQL will
give me a list of all tables in all databases? Show quote "Chandra" <chan***@discussions.microsoft.com> wrote in message news:D4A1E0C4-B45A-4614-8C3B-4E7CA7A5DA6B@microsoft.com... > hi > > probably this can help u > > select * > from systypes > where xusertype > 256 > order by name > > please let me knowif u have any questions > > > -- > best Regards, > Chandra > http://chanduas.blogspot.com/ > http://groups.msn.com/SQLResource/ > --------------------------------------- > > > > "arch" wrote: > >> Thanks for the assistance, but sp_help returns a whole bunch of things. >> How >> can I return ONLY a list of user-defined types? >> >> "Tom Moreau" <tom@dont.spam.me.cips.ca> wrote in message >> news:OqbJRdPlFHA.576@tk2msftngp13.phx.gbl... >> > Check out sp_help in the BOL. >> > >> > -- >> > Tom >> > >> > ---------------------------------------------------- >> > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA >> > SQL Server MVP >> > Columnist, SQL Server Professional >> > Toronto, ON Canada >> > www.pinpub.com >> > . >> > <arch> wrote in message news:42eb5cf6$1@funnel.arach.net.au... >> > I wanted to write a select query that returns a list of user-defined >> > types >> > only (without returning the system types). In other words, I can't >> > just >> > do >> > "select * from systypes". Does anyone know if this can be done? >> > >> > >> > >> > >> >> >> Try:
sp_MSforeachDB 'select * from ?.information_schema.tables where table_type = ''base table''' -- Tom ---------------------------------------------------- Thomas A. Moreau, BSc, PhD, MCSE, MCDBA SQL Server MVP Columnist, SQL Server Professional Toronto, ON Canada www.pinpub.com .. <arch> wrote in message news:42eb7e72@funnel.arach.net.au... Thanks. That did the trick. If I can ask one more question: What SQL willgive me a list of all tables in all databases? Show quote "Chandra" <chan***@discussions.microsoft.com> wrote in message news:D4A1E0C4-B45A-4614-8C3B-4E7CA7A5DA6B@microsoft.com... > hi > > probably this can help u > > select * > from systypes > where xusertype > 256 > order by name > > please let me knowif u have any questions > > > -- > best Regards, > Chandra > http://chanduas.blogspot.com/ > http://groups.msn.com/SQLResource/ > --------------------------------------- > > > > "arch" wrote: > >> Thanks for the assistance, but sp_help returns a whole bunch of things. >> How >> can I return ONLY a list of user-defined types? >> >> "Tom Moreau" <tom@dont.spam.me.cips.ca> wrote in message >> news:OqbJRdPlFHA.576@tk2msftngp13.phx.gbl... >> > Check out sp_help in the BOL. >> > >> > -- >> > Tom >> > >> > ---------------------------------------------------- >> > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA >> > SQL Server MVP >> > Columnist, SQL Server Professional >> > Toronto, ON Canada >> > www.pinpub.com >> > . >> > <arch> wrote in message news:42eb5cf6$1@funnel.arach.net.au... >> > I wanted to write a select query that returns a list of user-defined >> > types >> > only (without returning the system types). In other words, I can't >> > just >> > do >> > "select * from systypes". Does anyone know if this can be done? >> > >> > >> > >> > >> >> >> Thanks. That's good, but is there any way I can get that in one query (ie
one result set)? Show quote "Tom Moreau" <tom@dont.spam.me.cips.ca> wrote in message news:%23z7vxtQlFHA.3032@TK2MSFTNGP10.phx.gbl... > Try: > > sp_MSforeachDB 'select * from ?.information_schema.tables where table_type > = > ''base table''' > > -- > Tom > > ---------------------------------------------------- > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA > SQL Server MVP > Columnist, SQL Server Professional > Toronto, ON Canada > www.pinpub.com > . > <arch> wrote in message news:42eb7e72@funnel.arach.net.au... > Thanks. That did the trick. If I can ask one more question: What SQL > will > give me a list of all tables in all databases? > > > > "Chandra" <chan***@discussions.microsoft.com> wrote in message > news:D4A1E0C4-B45A-4614-8C3B-4E7CA7A5DA6B@microsoft.com... >> hi >> >> probably this can help u >> >> select * >> from systypes >> where xusertype > 256 >> order by name >> >> please let me knowif u have any questions >> >> >> -- >> best Regards, >> Chandra >> http://chanduas.blogspot.com/ >> http://groups.msn.com/SQLResource/ >> --------------------------------------- >> >> >> >> "arch" wrote: >> >>> Thanks for the assistance, but sp_help returns a whole bunch of things. >>> How >>> can I return ONLY a list of user-defined types? >>> >>> "Tom Moreau" <tom@dont.spam.me.cips.ca> wrote in message >>> news:OqbJRdPlFHA.576@tk2msftngp13.phx.gbl... >>> > Check out sp_help in the BOL. >>> > >>> > -- >>> > Tom >>> > >>> > ---------------------------------------------------- >>> > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA >>> > SQL Server MVP >>> > Columnist, SQL Server Professional >>> > Toronto, ON Canada >>> > www.pinpub.com >>> > . >>> > <arch> wrote in message news:42eb5cf6$1@funnel.arach.net.au... >>> > I wanted to write a select query that returns a list of user-defined >>> > types >>> > only (without returning the system types). In other words, I can't >>> > just >>> > do >>> > "select * from systypes". Does anyone know if this can be done? >>> > >>> > >>> > >>> > >>> >>> >>> > > Here ya go:
create table #t ( table_catalog sysname not null , table_name sysname not null ) exec sp_MSforeachDB 'insert #t select table_catalog, table_name from ?.information_schema.tables where table_type = ''base table''' select * from #t drop table #t -- Tom ---------------------------------------------------- Thomas A. Moreau, BSc, PhD, MCSE, MCDBA SQL Server MVP Columnist, SQL Server Professional Toronto, ON Canada www.pinpub.com .. <arch> wrote in message news:42eb842c$1@funnel.arach.net.au... Thanks. That's good, but is there any way I can get that in one query (ieone result set)? Show quote "Tom Moreau" <tom@dont.spam.me.cips.ca> wrote in message news:%23z7vxtQlFHA.3032@TK2MSFTNGP10.phx.gbl... > Try: > > sp_MSforeachDB 'select * from ?.information_schema.tables where table_type > = > ''base table''' > > -- > Tom > > ---------------------------------------------------- > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA > SQL Server MVP > Columnist, SQL Server Professional > Toronto, ON Canada > www.pinpub.com > . > <arch> wrote in message news:42eb7e72@funnel.arach.net.au... > Thanks. That did the trick. If I can ask one more question: What SQL > will > give me a list of all tables in all databases? > > > > "Chandra" <chan***@discussions.microsoft.com> wrote in message > news:D4A1E0C4-B45A-4614-8C3B-4E7CA7A5DA6B@microsoft.com... >> hi >> >> probably this can help u >> >> select * >> from systypes >> where xusertype > 256 >> order by name >> >> please let me knowif u have any questions >> >> >> -- >> best Regards, >> Chandra >> http://chanduas.blogspot.com/ >> http://groups.msn.com/SQLResource/ >> --------------------------------------- >> >> >> >> "arch" wrote: >> >>> Thanks for the assistance, but sp_help returns a whole bunch of things. >>> How >>> can I return ONLY a list of user-defined types? >>> >>> "Tom Moreau" <tom@dont.spam.me.cips.ca> wrote in message >>> news:OqbJRdPlFHA.576@tk2msftngp13.phx.gbl... >>> > Check out sp_help in the BOL. >>> > >>> > -- >>> > Tom >>> > >>> > ---------------------------------------------------- >>> > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA >>> > SQL Server MVP >>> > Columnist, SQL Server Professional >>> > Toronto, ON Canada >>> > www.pinpub.com >>> > . >>> > <arch> wrote in message news:42eb5cf6$1@funnel.arach.net.au... >>> > I wanted to write a select query that returns a list of user-defined >>> > types >>> > only (without returning the system types). In other words, I can't >>> > just >>> > do >>> > "select * from systypes". Does anyone know if this can be done? >>> > >>> > >>> > >>> > >>> >>> >>> > > Thanks. That's good stuff. I had hoped it could be done as a simple select
statement, but this still works. One minor problem though, an error is raised if the database name has spaces in it. Is there any way we can prevent that? Show quote "Tom Moreau" <tom@dont.spam.me.cips.ca> wrote in message news:ujbchCRlFHA.2484@TK2MSFTNGP15.phx.gbl... > Here ya go: > > create table #t > ( > table_catalog sysname not null > , table_name sysname not null > ) > > exec sp_MSforeachDB 'insert #t select table_catalog, table_name from > ?.information_schema.tables where table_type = ''base table''' > > select * from #t > > drop table #t > > > -- > Tom > > ---------------------------------------------------- > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA > SQL Server MVP > Columnist, SQL Server Professional > Toronto, ON Canada > www.pinpub.com > . > <arch> wrote in message news:42eb842c$1@funnel.arach.net.au... > Thanks. That's good, but is there any way I can get that in one query (ie > one result set)? > > "Tom Moreau" <tom@dont.spam.me.cips.ca> wrote in message > news:%23z7vxtQlFHA.3032@TK2MSFTNGP10.phx.gbl... >> Try: >> >> sp_MSforeachDB 'select * from ?.information_schema.tables where >> table_type >> = >> ''base table''' >> >> -- >> Tom >> >> ---------------------------------------------------- >> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA >> SQL Server MVP >> Columnist, SQL Server Professional >> Toronto, ON Canada >> www.pinpub.com >> . >> <arch> wrote in message news:42eb7e72@funnel.arach.net.au... >> Thanks. That did the trick. If I can ask one more question: What SQL >> will >> give me a list of all tables in all databases? >> >> >> >> "Chandra" <chan***@discussions.microsoft.com> wrote in message >> news:D4A1E0C4-B45A-4614-8C3B-4E7CA7A5DA6B@microsoft.com... >>> hi >>> >>> probably this can help u >>> >>> select * >>> from systypes >>> where xusertype > 256 >>> order by name >>> >>> please let me knowif u have any questions >>> >>> >>> -- >>> best Regards, >>> Chandra >>> http://chanduas.blogspot.com/ >>> http://groups.msn.com/SQLResource/ >>> --------------------------------------- >>> >>> >>> >>> "arch" wrote: >>> >>>> Thanks for the assistance, but sp_help returns a whole bunch of things. >>>> How >>>> can I return ONLY a list of user-defined types? >>>> >>>> "Tom Moreau" <tom@dont.spam.me.cips.ca> wrote in message >>>> news:OqbJRdPlFHA.576@tk2msftngp13.phx.gbl... >>>> > Check out sp_help in the BOL. >>>> > >>>> > -- >>>> > Tom >>>> > >>>> > ---------------------------------------------------- >>>> > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA >>>> > SQL Server MVP >>>> > Columnist, SQL Server Professional >>>> > Toronto, ON Canada >>>> > www.pinpub.com >>>> > . >>>> > <arch> wrote in message news:42eb5cf6$1@funnel.arach.net.au... >>>> > I wanted to write a select query that returns a list of user-defined >>>> > types >>>> > only (without returning the system types). In other words, I can't >>>> > just >>>> > do >>>> > "select * from systypes". Does anyone know if this can be done? >>>> > >>>> > >>>> > >>>> > >>>> >>>> >>>> >> >> > > I'm just about to run off for the weekend but you may want to alter the
select: select table_catalog, table_name to add square brackets to each item. -- Tom ---------------------------------------------------- Thomas A. Moreau, BSc, PhD, MCSE, MCDBA SQL Server MVP Columnist, SQL Server Professional Toronto, ON Canada www.pinpub.com .. <arch> wrote in message news:42eb9180$1@funnel.arach.net.au... Thanks. That's good stuff. I had hoped it could be done as a simple selectstatement, but this still works. One minor problem though, an error is raised if the database name has spaces in it. Is there any way we can prevent that? Show quote "Tom Moreau" <tom@dont.spam.me.cips.ca> wrote in message news:ujbchCRlFHA.2484@TK2MSFTNGP15.phx.gbl... > Here ya go: > > create table #t > ( > table_catalog sysname not null > , table_name sysname not null > ) > > exec sp_MSforeachDB 'insert #t select table_catalog, table_name from > ?.information_schema.tables where table_type = ''base table''' > > select * from #t > > drop table #t > > > -- > Tom > > ---------------------------------------------------- > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA > SQL Server MVP > Columnist, SQL Server Professional > Toronto, ON Canada > www.pinpub.com > . > <arch> wrote in message news:42eb842c$1@funnel.arach.net.au... > Thanks. That's good, but is there any way I can get that in one query (ie > one result set)? > > "Tom Moreau" <tom@dont.spam.me.cips.ca> wrote in message > news:%23z7vxtQlFHA.3032@TK2MSFTNGP10.phx.gbl... >> Try: >> >> sp_MSforeachDB 'select * from ?.information_schema.tables where >> table_type >> = >> ''base table''' >> >> -- >> Tom >> >> ---------------------------------------------------- >> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA >> SQL Server MVP >> Columnist, SQL Server Professional >> Toronto, ON Canada >> www.pinpub.com >> . >> <arch> wrote in message news:42eb7e72@funnel.arach.net.au... >> Thanks. That did the trick. If I can ask one more question: What SQL >> will >> give me a list of all tables in all databases? >> >> >> >> "Chandra" <chan***@discussions.microsoft.com> wrote in message >> news:D4A1E0C4-B45A-4614-8C3B-4E7CA7A5DA6B@microsoft.com... >>> hi >>> >>> probably this can help u >>> >>> select * >>> from systypes >>> where xusertype > 256 >>> order by name >>> >>> please let me knowif u have any questions >>> >>> >>> -- >>> best Regards, >>> Chandra >>> http://chanduas.blogspot.com/ >>> http://groups.msn.com/SQLResource/ >>> --------------------------------------- >>> >>> >>> >>> "arch" wrote: >>> >>>> Thanks for the assistance, but sp_help returns a whole bunch of things. >>>> How >>>> can I return ONLY a list of user-defined types? >>>> >>>> "Tom Moreau" <tom@dont.spam.me.cips.ca> wrote in message >>>> news:OqbJRdPlFHA.576@tk2msftngp13.phx.gbl... >>>> > Check out sp_help in the BOL. >>>> > >>>> > -- >>>> > Tom >>>> > >>>> > ---------------------------------------------------- >>>> > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA >>>> > SQL Server MVP >>>> > Columnist, SQL Server Professional >>>> > Toronto, ON Canada >>>> > www.pinpub.com >>>> > . >>>> > <arch> wrote in message news:42eb5cf6$1@funnel.arach.net.au... >>>> > I wanted to write a select query that returns a list of user-defined >>>> > types >>>> > only (without returning the system types). In other words, I can't >>>> > just >>>> > do >>>> > "select * from systypes". Does anyone know if this can be done? >>>> > >>>> > >>>> > >>>> > >>>> >>>> >>>> >> >> > > Hi,
List tables inside one database sp_tables or select TABLE_NAME from INFORMATION_SCHEMA.TABLES List tables from all databases EXEC sp_msforeachdb 'EXEC ?..sp_tables' Thanks Hari SQL Server MVP Show quote <arch> wrote in message news:42eb7e72@funnel.arach.net.au... > Thanks. That did the trick. If I can ask one more question: What SQL > will give me a list of all tables in all databases? > > > > "Chandra" <chan***@discussions.microsoft.com> wrote in message > news:D4A1E0C4-B45A-4614-8C3B-4E7CA7A5DA6B@microsoft.com... >> hi >> >> probably this can help u >> >> select * >> from systypes >> where xusertype > 256 >> order by name >> >> please let me knowif u have any questions >> >> >> -- >> best Regards, >> Chandra >> http://chanduas.blogspot.com/ >> http://groups.msn.com/SQLResource/ >> --------------------------------------- >> >> >> >> "arch" wrote: >> >>> Thanks for the assistance, but sp_help returns a whole bunch of things. >>> How >>> can I return ONLY a list of user-defined types? >>> >>> "Tom Moreau" <tom@dont.spam.me.cips.ca> wrote in message >>> news:OqbJRdPlFHA.576@tk2msftngp13.phx.gbl... >>> > Check out sp_help in the BOL. >>> > >>> > -- >>> > Tom >>> > >>> > ---------------------------------------------------- >>> > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA >>> > SQL Server MVP >>> > Columnist, SQL Server Professional >>> > Toronto, ON Canada >>> > www.pinpub.com >>> > . >>> > <arch> wrote in message news:42eb5cf6$1@funnel.arach.net.au... >>> > I wanted to write a select query that returns a list of user-defined >>> > types >>> > only (without returning the system types). In other words, I can't >>> > just >>> > do >>> > "select * from systypes". Does anyone know if this can be done? >>> > >>> > >>> > >>> > >>> >>> >>> > > Thanks. That's all good stuff, but is it possible to have all the results
in one query, with a database column and a table column? Show quote "Hari Prasad" <hari_prasa***@hotmail.com> wrote in message news:%23co%23kzQlFHA.2396@TK2MSFTNGP12.phx.gbl... > Hi, > > List tables inside one database > > sp_tables > or > select TABLE_NAME from INFORMATION_SCHEMA.TABLES > > List tables from all databases > > EXEC sp_msforeachdb 'EXEC ?..sp_tables' > > > Thanks > Hari > SQL Server MVP > > > <arch> wrote in message news:42eb7e72@funnel.arach.net.au... >> Thanks. That did the trick. If I can ask one more question: What SQL >> will give me a list of all tables in all databases? >> >> >> >> "Chandra" <chan***@discussions.microsoft.com> wrote in message >> news:D4A1E0C4-B45A-4614-8C3B-4E7CA7A5DA6B@microsoft.com... >>> hi >>> >>> probably this can help u >>> >>> select * >>> from systypes >>> where xusertype > 256 >>> order by name >>> >>> please let me knowif u have any questions >>> >>> >>> -- >>> best Regards, >>> Chandra >>> http://chanduas.blogspot.com/ >>> http://groups.msn.com/SQLResource/ >>> --------------------------------------- >>> >>> >>> >>> "arch" wrote: >>> >>>> Thanks for the assistance, but sp_help returns a whole bunch of things. >>>> How >>>> can I return ONLY a list of user-defined types? >>>> >>>> "Tom Moreau" <tom@dont.spam.me.cips.ca> wrote in message >>>> news:OqbJRdPlFHA.576@tk2msftngp13.phx.gbl... >>>> > Check out sp_help in the BOL. >>>> > >>>> > -- >>>> > Tom >>>> > >>>> > ---------------------------------------------------- >>>> > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA >>>> > SQL Server MVP >>>> > Columnist, SQL Server Professional >>>> > Toronto, ON Canada >>>> > www.pinpub.com >>>> > . >>>> > <arch> wrote in message news:42eb5cf6$1@funnel.arach.net.au... >>>> > I wanted to write a select query that returns a list of user-defined >>>> > types >>>> > only (without returning the system types). In other words, I can't >>>> > just >>>> > do >>>> > "select * from systypes". Does anyone know if this can be done? >>>> > >>>> > >>>> > >>>> > >>>> >>>> >>>> >> >> > >  |
|||||||||||||||||||||||
Other interesting topics