Get the Information about all the databases available in your SQL Server
EXEC sp_databases
EXEC sp_helpdb
select * from master..sysdatabases
SELECT * FROM sys.databases
SELECT * FROM sys.sysdatabases
EXEC sp_msForEachDB 'PRINT ''?'''
Get the Information about all the tables available in your SQL Server Database
SELECT Owner = TABLE_SCHEMA, TableName = TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND OBJECTPROPERTY(OBJECT_ID(TABLE_NAME), 'IsMsShipped') = 0 ORDER BY TABLE_SCHEMA, TABLE_NAME
or
exec sp_tables
but with this you have to filter to exclude the table owned by (
TABLE_OWNER) 'sys','INFORMATION_SCHEMA'.
Get the Information about all the tables available in your SQL Server Database
sp_columns @tableName
Select * from Information_Schema.Columns where Table_Name=@tableName
Showing posts with label Error sql server. Show all posts
Showing posts with label Error sql server. Show all posts
Thursday, May 1, 2008
Wednesday, April 16, 2008
Problem Numeric check in SQL Server
In my datase i had a table which contains both numeric and characters and the table datatype was Varchar, and i have to find the min of the of the numeric value. In sql server the function ISNUMERIC(expr) was the solution to get rid of the character value.
The ISNUMERIC function determines whether an expression is a valid numeric type. returns 1 when the input expression evaluates to a valid numeric data type; otherwise it returns 0. So i used the query like below
Select min(ColName) from demo where ISNUMERIC(ColName)=1
And i was getting my desired value.
But one day it started an exception in my asp.net page. And i could not find the reason.
After a long debugging i found that it was the problem with ISNUMERIC function of sql server. The ISNUMERIC function is returning 1 for some non-numeric character like '-' i.e
Select Isnumeric('-') returns 1 not 0.
And it was returning '-' from the query and error in asp.net page.
Then i tried with casting the result to int as.
Select Cast(min(ColName) as int) from demo where ISNUMERIC(ColName)=1
And in this way the error was solved but the min value was always 0 as Cast('-')=0
Then i identified this abnormal characters as excluded them through the where clause.
Select Cast(min(ColName) as int) from demo where ISNUMERIC(ColName)=1 and eadr not in('+','-','$').
This behaviour occurs for these three charactes('+','-','$') i have found so far.
For more information check this link
The ISNUMERIC function determines whether an expression is a valid numeric type. returns 1 when the input expression evaluates to a valid numeric data type; otherwise it returns 0. So i used the query like below
Select min(ColName) from demo where ISNUMERIC(ColName)=1
And i was getting my desired value.
But one day it started an exception in my asp.net page. And i could not find the reason.
After a long debugging i found that it was the problem with ISNUMERIC function of sql server. The ISNUMERIC function is returning 1 for some non-numeric character like '-' i.e
Select Isnumeric('-') returns 1 not 0.
And it was returning '-' from the query and error in asp.net page.
Then i tried with casting the result to int as.
Select Cast(min(ColName) as int) from demo where ISNUMERIC(ColName)=1
And in this way the error was solved but the min value was always 0 as Cast('-')=0
Then i identified this abnormal characters as excluded them through the where clause.
Select Cast(min(ColName) as int) from demo where ISNUMERIC(ColName)=1 and eadr not in('+','-','$').
This behaviour occurs for these three charactes('+','-','$') i have found so far.
For more information check this link
Subscribe to:
Posts (Atom)
