Information_schema
Mar 28, 2008 Hi to all,
I'm using SQL Server 2005. May I know where the INFORMATION_SCHEMA
is found, can we view this by object explorer.
ADVERTISEMENT
Information_schema ?
Feb 24, 2004Hello,
When the procedure : sp_tables is executed (master db), the table owners are either dbo or INFORMATION_SCHEMA,
any detail about this last ? (to be precise, table_type is view and not table).
Thanks
Information_schema.columns
Oct 18, 2005Hi:
I need to change a column default to '' and not null for 1500 databases accross 10 servers.
if exists (select * from information_schema.columns
where table_name = 'tblABC'
and column_name = 'columnX'
and data_type = 'VARCHAR'
and is_nullable = 'No'
and column_default = '('')'
)
begin
--do something to implement
end
Here, there is a problem for and column_default = '('')'
I have tried ''''+'('')' + '''' or "'('')'", neither works.
Do you have any idea to deal with the ('') ?
thanks
-D
Using INFORMATION_SCHEMA With An ACCESS DB
Feb 13, 2007I'm having a really hard time displaying the structure of my database using INFORMATION_SCHEMA. Can this be used with Access? I've used it before for sql databases but when I try it with this access one I get an error message telling me: Can't find file c:...INFORMATION_SCHEMA.mdb
I'm hoping maybe there's another command that works just as well or some other way to get around it.
I'm using Visual Web Developer 2005 Express
and my .aspx pages are coded using VB so any insights would be awesome!
Thanks much,
~UNI
Information_schema Permissions
Jul 20, 2005Hi I need to see all the indexes in a database. The ID has dbo rightsto the database, but not to the master. I can't see anything inINFORMATION_SCHEMA.CHECK_CONSTRAINTS orINFORMATION_SCHEMA.KEY_COLUMN_USAGEAn sa ID for the master sees everything however.Thanks for your helpPachydermitis
View 5 Replies View RelatedQuerying Information_schema
Oct 24, 2007Hi,
I'd like to get information about tables defined in the SqlCe20 database. So I tried to use the following which doesn't work:
string sql = "SELECT COLUMN_NAME AS CN FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = ?"cmd = new SqlCeCommand(sql, connection);cmd.Parameters.Add(newSqlCeDataParameter("A", DbTypes.NVarChar);cmd.Prepare();//later in codecmd.Parameters[0].Value = tableName;reader = cmd.ExecuteReader();while (reader.Read())....
Here the reader.Read() returns always false indepent of the table name.
In contrast doing the following without parameter works as expected:
reader = cmd.ExecuteReader(string.Format("SELECT COLUMN_NAME AS CN FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '{0}'", tableName));
while (reader.Read())
Because I have to execute this statemen often I have to use prepared statement with parameters.
How can I get it working?
Miroslaw
INFORMATION_SCHEMA Login In SysUsers?
Dec 26, 2001Hiya,
I'm trying to run reports, some of which have UserEntered as a criteria. So I'm filling up a list with (SELECT * FROM SysUsers WHERE IsLogin = 1), which is also pulling up INFORMATION_SCHEMA as a login. I'm not really sure why this would be an islogin = 1 or issqluser = 1. Is this a server default login,and if yes,why, and what can I do about it?
Thanks,
Sarah
SQL2005 Information_schema && Default
May 10, 2006Hi:
from SQL2005 information_schema, I don't see anything related to default constraint.
So, I still need the old way to use old stuff such as
from sysobjects sobj inner join syscolumns scolumn.
I need to check existing 3000 databases to drop any possible default for tableA.columnB's default and then add a new one.
I thought SQL2005 will stop let us to query system table to retrieve table structure info including default, but only via information_schema? :rolleyes:
thanks
David
Information_schema For Temp Table ?
Nov 26, 2007Hi there!
I'm trying to find how can I get the information_schema for a temp table.
I'm trying to find all columns of a temp table.
So it will be something like this SELECT * FROM information_schema.columns
But it doesn't work for temp table, I tried tempdb.dbo.information_schema.columns .... nada....
Please help!
Thanks,
Or Tho
How To View INFORMATION_SCHEMA.TABLES
Jun 20, 2007Hi:
I am new to SQLSERVER, so I am trying to learn from all these database views, in Oracle it use 'desc all_tables ' to database dictionary view, can some one tell me how to view SQLserver dictionary view like INFORMATION_SCHEMA.TABLES, or sys.tables?
IF I login as sa, but I only want to view the table list one schema at a time? how do I do that?
I tried :
select table_name from INFORMATION_SCHEMA.TABLES
where table_schema='CIT'....
select table_name from INFORMATION_SCHEMA.TABLES
where table_schema='CIT.DBO'.... give 0 result too.
it give me 0 result, but if I login as CIT, then
'select table_name from INFORMATION_SCHEMA.TABLES' will give 14 tables.
Plus, When I log into Query analyzer, all the tables has dbo. prefix. , why is that?
Thanks a lot
Information_schema.tables, Triggers, Cursors
Jul 8, 2003how do i return only the tables created by the user?
in three of my databases i am inserting one record per 5 secs. in all the tables. how good is using triggers for 'insert instead of' for these tables?
Need To Access INFORMATION_SCHEMA On Linked Server
Apr 5, 2006Hi,
I'm working with MSSQL2K+SP3a, Standard Edition. I defined linked server (MSDE).
On the local server, I can do
select * from [testDB].[INFORMATION_SCHEMA].[TABLES] -- local
select * from [testSRV].[testDB_far].[dbo].[sysobjects] -- linked
but not
select * from [testSRV].[testDB_far].[INFORMATION_SCHEMA].[TABLES]
How can I access the INFORMATION_SCHEMA on the linked server ?
Thanks,
Helena
Invalid Object Name 'INFORMATION_SCHEMA.tables'
Nov 30, 2007The following code example to returns table space usage in my databases, except for AdventureWorks.
When I "USE AdventureWorks", I get the following message. "Invalid object name 'INFORMATION_SCHEMA.tables'."
Why is AdventureWorks different? Thanks. Bill
USE AdventureWorks
-- Declare variables
DECLARE @AWTables TABLE (SchemaTable varchar(100))
DECLARE @TableName varchar(100)
--Insert Table names into the TABLE variable
INSERT @AWTables
(SchemaTable)
SELECT TABLE_SCHEMA + '.' + TABLE_NAME
FROM INFORMATION_SCHEMA.tables
WHERE TABLE_TYPE = 'BASE TABLE'
ORDER BY TABLE_SCHEMA + '.' + TABLE_NAME
-- Report on each table using sp_spaceused
WHILE (SELECT COUNT(*) FROM @AWTables) > 0
BEGIN
SELECT TOP 1 @TableName = SchemaTable
FROM @AWTables
ORDER BY SchemaTable
EXEC sp_spaceused @TableName
DELETE @AWTables
WHERE SchemaTable = @TableName
END
ForeignKey Using INFORMATION_SCHEMA.TABLES As PrimaryKeyTable
Oct 12, 2006Hi,
I have a "master" table that holds the names of data tables (one record in the "master" table for each "data" table).
Can I create a ForeignKey constraint that will prevent the "master" table records from being removed if the cooresponding "data" table exists? Is the way to do this to use INFORMATION_SCHEMA.TABLES as the PrimaryKeyTable for the ForeignKey?
Thanks!
INFORMATION_SCHEMA Query Question: Constraint Columns
Jul 20, 2005Hi Folks:I'm a little new to SQLServer, so please pardon my ignorance!I've found the INFORMATION_SCHEMA views for TABLES, COLUMNS, andTABLE_CONSTRAINTS. I'm looking for the views that will give me the list ofcolumns by constraint.For instance, if Table1 has a unique key called Table1_UK01, I can find thatunder INFORMATION_SCHEMA.TABLE_CONSTRAINTS. But I also need to know thecolumns in that UK constraint. I've triedINFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE andINFORMATION_SCHEMA.KEY_COLUMN_USAGE, but the UK I have defined for this usertable doesn't seem to show up in either of those views.Can anyone point me in the right direction? Any sample queries would betremendously appreciated. I'm going to be using this meta-data toautomatically generate quite a bundle of stored procs that do updates basedon finding rows via unique keys...TIA,Dave
View 3 Replies View RelatedRestricting Access To Sys And INFORMATION_SCHEMA Views In ODBC
Oct 23, 2007Hi
I'm building a data warehouse - my end users connect using Access via ODBC Microsoft SQL Server driver (2000.85.1117.00).
However, whenever they connect using Access via ODBC they get a huge list of sys and INFORMATION_SCHEMA views, in addition to the data warehouse tables they need to access.
How can I remove these sys and INFORMATION_SCHEMA views from the list of tables/views presented to the end user?
I've tried denying access by changing permissions to deny in the public role of the master database - I have also changed permissions in the public role in the data warehouse database. When I do this, the ODBC connection fails to retrieve any objects because it doesn't have access to sys.databases (and various other unspecified objects).
I'm stuck - help!
Querying Information_Schema.Routines Shows Incomplete Information
Oct 14, 2007We have a legacy app where some of our web page urls were hardcoded into the stored procs (SQL 2000 SP4 database). We have changed the system and so changed the hardcoded strings with a value stored in a config table. We used the following query to identify the hardcoded urls (say LegacyPage.asp) €“
select routine_name
from information_schema.routines
where routine_definition like '%LegacyPage.asp?%'
However, even after this we keep getting issues with LegacyPage.asp being referenced. Tracing the code, I found that there is at least one SP (say spHardCoded) which does not turn up in the query, but does have the string LegacyPage.asp? in the routine definition. When I run the following query €“
select routine_name
from information_schema.routines
where
routine_name = 'spHardCoded'
and routine_definition like '%LegacyPage.asp?%'
0 rows are returned!
Am I missing something obvious here or is INFORMATION_SCHEMA.Routines metadata not always updated? Is there any way to force the metadata to be updated, before we query it? Is there a better system catalog view which lets me do the same thing? Any help would be really appreciated.
SQL2005 Collation Problem Between Fn_ListExtendedProperty And Information_schema.Tables ?
Apr 25, 2008Hi,
we've just installed SQL2005 and, as expected, are hitting problems with collation settings.
We have made sure that the collation of our new server is the same as the old (working) SQL2000 server (both are Latin1_General_CI_AS) but a function that works fine on the 2000 box just doesn't work on the 2005 box.
The problem is this...
Code Snippet
select *
from information_schema.[tables] as t
left join :: fn_ListExtendedProperty('MS_Description','user','dbo','table',null,null,null) as xp on ( xp.objname = t.table_name )
...where that string comparison on "table_name" results in...
"Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "Latin1_General_CI_AI" in the equal to operation."
Now... I can't see where the "Latin1_General_CI_AI" is coming from. The two data sources are both "system" sources, so should, I would have thought, been the same. If I do...
execute sp_help "information_schema.tables"
...all the columns are Latin1_General_CI_AS. If I do...
execute sp_help fn_ListExtendedProperty
...the same collation is returned for all columns.
I've checked everything I can think of (server, master, tempdb, etc.), and everything seems to be "AS".
Where might this "AI" collation be defined ? We have only just installed 2005, so a re-install, while a pain, is not a massive problem, but what should we check/change ?
Thanks
COLID Discrepancy In Syscolumns / Information_Schema.Columns - SQL Server 2000
Jul 20, 2005I ran the following query in Query Analyzer for a 7 column table.SELECT c.name,c.colid FROM syscolumns c WHERE c.id=925962375 ORDER BYc.colidThe results were:I_CSD 1X_STE_XML2I_USR_LCK4T_CRT_RCD5I_USR_CRT_RCD6T_UDT_RCD7I_USR_UDT_RCD8If I use the information_schema view (SELECT column_name,ordinal_position FROM information_schema.columns WHERE table_name ='CSD_XML') I get the same results.The problem is that the colids go from 2 to 4 and the colids gothrough 8 when there are only 7 columns.At one time there was another column in the table, but it has sincebeen dropped and isn't there anymore. It seems that the colids insyscolumns did not update when the column was dropped.Is this because of the way I dropped the column? Is there anything Ican do now that it has happened?
View 3 Replies View Related