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.


 

View 2 Replies


ADVERTISEMENT

Information_schema ?

Feb 24, 2004

Hello,

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

View 1 Replies View Related

Information_schema.columns

Oct 18, 2005

Hi:

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

View 3 Replies View Related

Using INFORMATION_SCHEMA With An ACCESS DB

Feb 13, 2007

I'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

View 1 Replies View Related

Information_schema Permissions

Jul 20, 2005

Hi 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 Related

Querying Information_schema

Oct 24, 2007

Hi,

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

View 2 Replies View Related

INFORMATION_SCHEMA Login In SysUsers?

Dec 26, 2001

Hiya,
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

View 1 Replies View Related

SQL2005 Information_schema && Default

May 10, 2006

Hi:

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

View 3 Replies View Related

Information_schema For Temp Table ?

Nov 26, 2007

Hi 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

View 9 Replies View Related

How To View INFORMATION_SCHEMA.TABLES

Jun 20, 2007

Hi:

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

View 3 Replies View Related

Information_schema.tables, Triggers, Cursors

Jul 8, 2003

how 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?

View 5 Replies View Related

Need To Access INFORMATION_SCHEMA On Linked Server

Apr 5, 2006

Hi,
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

View 3 Replies View Related

Invalid Object Name 'INFORMATION_SCHEMA.tables'

Nov 30, 2007

The 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

View 4 Replies View Related

ForeignKey Using INFORMATION_SCHEMA.TABLES As PrimaryKeyTable

Oct 12, 2006

Hi,

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!

View 1 Replies View Related

INFORMATION_SCHEMA Query Question: Constraint Columns

Jul 20, 2005

Hi 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 Related

Restricting Access To Sys And INFORMATION_SCHEMA Views In ODBC

Oct 23, 2007

Hi
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!

View 4 Replies View Related

Querying Information_Schema.Routines Shows Incomplete Information

Oct 14, 2007

We 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.

View 3 Replies View Related

SQL2005 Collation Problem Between Fn_ListExtendedProperty And Information_schema.Tables ?

Apr 25, 2008

Hi,

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

View 1 Replies View Related

COLID Discrepancy In Syscolumns / Information_Schema.Columns - SQL Server 2000

Jul 20, 2005

I 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







Copyrights 2005-15 www.BigResource.com, All rights reserved