GetSchema

Should the compact edition connection support the GetSchema method? When I call it gives me a not supported exception. I am after the table names in my database. I could do a query on Information_schema.tables, but I thought using GetSchema("Tables") would be a more generic way to get the information.

Thanks,

Juan Foegen

It is a bug, see http://support.microsoft.com/kb/912435. Use INFORMATION_SCHEMA.TABLES instead (it is a standard way of getting schema information)

Get uniqueness of a column from the system tables or information_schema

I'm trying to write a query which, from a given table name, will
produce a list of column names with an indicator as to whether it is
unique. By unique, I mean it a) is the column in a single-column
primary key, b) is the column in a single-column unique constraint, or
c) is the column in single-column unique index.
So, for this DDL,
-- CODE BEGINS
create table t1 (
c1 int not null primary key,
c2 int not null unique,
c3 int not null,
c4 int not null
)
create unique index ix1 on t1 (c3)
-- drop table t1
-- CODE ENDS
I'd like a query that will produce something like this output
c1 yes
c2 yes
c3 yes
c4 no
I've spent a few hours with sysobjects, sysindexes, sysconstraints, and
information_schema, but I'm getting nowhere. Anyone have any hints?
Thomas BergI forgot to say: I'm using SQL Server 2000 SP4.|||Hello, Thomas
This query returns the desired result:
SELECT name,
CASE WHEN EXISTS (
SELECT * FROM sysindexkeys k
INNER JOIN sysindexes i
ON k.id=i.id AND k.indid=i.indid
WHERE k.id=c.id AND k.colid=c.colid
AND INDEXPROPERTY(i.id,i.name,'IsUnique')=1
AND NOT EXISTS (
SELECT * FROM sysindexkeys k2
WHERE k.id=k2.id AND k.indid=k2.indid
AND k.keyno<>k2.keyno
)
) THEN 'yes' ELSE 'no' END AS IsUnique
FROM syscolumns c WHERE id=OBJECT_ID('t1')
Note that it's sufficient to search only for unique indexes, because
primary keys and unique keys are always enforced by creating a unique
index with the same name on the specified columns.
For a more thorough testing of the query, I added the following:
create unique index ix2 on t1 (c4,c3)
create index ix3 on t1 (c4)
Razvan|||tbergNoSpamPlease@.insight-system.co.jp a crit :
> I'm trying to write a query which, from a given table name, will
> produce a list of column names with an indicator as to whether it is
> unique. By unique, I mean it a) is the column in a single-column
> primary key, b) is the column in a single-column unique constraint, or
> c) is the column in single-column unique index.
> So, for this DDL,
> -- CODE BEGINS
> create table t1 (
> c1 int not null primary key,
> c2 int not null unique,
> c3 int not null,
> c4 int not null
> )
> create unique index ix1 on t1 (c3)
> -- drop table t1
> -- CODE ENDS
> I'd like a query that will produce something like this output
> c1 yes
> c2 yes
> c3 yes
> c4 no
> I've spent a few hours with sysobjects, sysindexes, sysconstraints, and
> information_schema, but I'm getting nowhere. Anyone have any hints?
> Thomas Berg
>
Here is a very general query wich give you all informations about
indexes with columns and uniqueness
SELECT
u.name AS IXD_SCHEMA_NAME,
o.name AS IXD_TABLE_NAME,
i.name AS IXD_INDEX_NAME,
CONSTRAINT_TYPE AS IXD_CONSTRAINT_TYPE,
CASE
WHEN i.indid = 0 THEN 'TABLE'
WHEN i.indid = 1 THEN 'CLUSTER'
WHEN i.indid BETWEEN 2 AND 254 THEN 'HEAP'
WHEN i.indid = 255 THEN 'TXTEIMAGE'
END AS IXD_INDEX_TYPE,
INDEXPROPERTY(o.id, i.name, 'IsUnique') AS IXD_IS_UNIQUE,
INDEXPROPERTY(o.id, i.name, 'IndexFillFactor') AS IXD_FILL_FACTOR,
c.name AS IXD_COL_NAME,
DATA_TYPE + '('+
CAST(COALESCE(CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION) AS
VARCHAR(16))
+ COALESCE(', '+CAST(NULLIF(NUMERIC_SCALE, 0) AS
VARCHAR(16)) , '') +')' AS IXD_COL_TYPE,
k.keyno AS IXD_COL_IDX_ORDER,
CASE
WHEN INDEXKEY_PROPERTY (o.id , i.indid , k.colid ,
N'isdescending' ) = 0 THEN 'ASC'
WHEN INDEXKEY_PROPERTY (o.id , i.indid , k.colid ,
N'isdescending' ) = 1 THEN 'DESC'
WHEN INDEXKEY_PROPERTY (o.id , i.indid , k.colid ,
N'isdescending' ) IS NULL THEN ''
END AS IXD_COL_DATA_ORDER,
INDEXPROPERTY(o.id, i.name, 'IsRowLockDisallowed') AS
IXD_ROW_LOCK_DISALLOWED,
INDEXPROPERTY(o.id, i.name, 'IsPageLockDisallowed') AS
IXD_PAGE_LOCK_DISALLOWED
FROM dbo.sysindexes i
INNER JOIN dbo.sysobjects o
ON i.id = o.id
INNER JOIN dbo.sysusers u
ON o.uid = u.uid
INNER JOIN dbo.sysindexkeys k
ON o.id = k.id
and i.indid = k.indid
INNER JOIN dbo.syscolumns c
ON k.colid = c.colid
and o.id = c.id
INNER JOIN INFORMATION_SCHEMA.COLUMNS ISC
ON u.name = ISC.TABLE_SCHEMA
AND o.name = ISC.TABLE_NAME
AND c.name = ISC.COLUMN_NAME
LEFT OUTER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TCT
ON u.name = TCT.CONSTRAINT_SCHEMA
AND i.name = TCT.CONSTRAINT_NAME
WHERE i.status & 64 <> 64 -- sauf les index "stat"
A +
Frdric BROUARD, MVP SQL Server, expert bases de donnes et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modlisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************|||You guys are brilliant. Thanks.

Get the specific files from directory

Hi,

I have some some files names in SQL DATABASE but my actuall files are keep in a seperate folder. so please help me that becasue i dont know that how i can select the specific files from folder and fetch into the imageArray according to the database table. In my coding its get the all files from directory but i want to get from database with where class and then select from directory.

The structure of my table is

create table event_pic
(
event_sub_id integer,
event_pic_name varchar(50)
)

here is code below:

Sub displayMe()

dim con as new SQLConnection("server=london-home; Database=tony; uid=rashid2; pwd=test; ")
dim cmd as new SQLCommand("select * from event_pic where event_sub_id='5' ",con)

con.open()

dim SDR as SQLDataReader
SDR = cmd.ExecuteReader()



con.close()


Dim imageArray() As String
Dim i As Integer

' grab full path and file of images on server in images folder
imageArray = Directory.GetFiles(Server.MapPath("upload/"), "*.*")

' remove the full path from the image filenames
For i = 0 To (imageArray.Length - 1)
imageArray(i) = Replace(imageArray(i), Server.MapPath("upload/"), "")

Next


ViewImages.DataSource = imageArray
ViewImages.DataBind()


End Sub

If you are using 2.0 you can solve it with a List.

Sub displayMe()Dim conAs New SqlConnection("server=london-home; Database=tony; uid=rashid2; pwd=test; ")Dim cmdAs New SqlCommand("select * from event_pic where event_sub_id='5' ", con) con.Open() Dim SDR As SqlDataReader SDR = cmd.ExecuteReader() Dim imageList As New List(Of String)() While SDR.Read() imageList.Add(SDR("event_pic_name").ToString())End While ViewImages.DataSource = imageList ViewImages.DataBind() con.Close()End Sub

|||

but the thing is that the database table only keeps the name of images actuall images are in a seperate folder and I will trace from folder according to the sql query.

|||

? In your initial query you strip out the path of the image, so imageArray will only contain the actual name of the image - without path. How do you want it? Does the entries in the database contain full path? Do you want to strip the path prior to binding?

|||

In my first query I get the files from DIRECTORY.GETFILES and then from loop I remove the paths and then bind to datagrid. anyway just pls tell me that the script that how I can get the specific files from folder according to the sql query?

|||

my database entries only keep only the names of files not full path

|||

If all your files are placed in the same folder, you can simply prefix the path of the image with that folder name. You should not use Server.MapPath for this, as it translates to a physical path. You need a virtual path in order serve the image URL correctly to the client. Also you must make sure that the image folder is located under the web root, either physically or through a virtual directory.

To get the correct virtual path in an asp.net app, you should use Page.ResolveUrl method.

So, the updated code would be:

imageList.Add(Page.ResolveUrl("~/imagefolder/") + SDR("event_pic_name").ToString())

|||

when i declare the image list its give me this error

Dim imageList As New List(Of String)()

Error: Type 'List' is not defined.|||

imports System.Collections.Generic

(Requires .net 2.0 or higher)

|||

Ok, Thanks but its display no images when i set the

ViewImage.DataList = imageList

ViewImage.Databound()

<asp:DataGrid runat="server" ID="ViewImages" AutoGenerateColumns="false" >
<columns>
<asp:TemplateColumn>
<itemtemplate>
<table id="tt" border="1" cellpadding="5" cellspacing="0">
<tr>
<td width="210"><img src="http://pics.10026.com/?src=upload/<%# Container.dataItem() %>" border="0" /></td>
</tr>
</table>
</itemtemplate>
</asp:TemplateColumn>
</columns>
</asp:DataGrid>

|||

You already have the image path specified in your asp.net code. You have to choose - either you have it there, or you have it in your page class. Can't have both, or else the path will be invalid. My suggestion is that you set the entire path from your page class, with the code I provided (Page.ResolveUrl). If you do a view source on the page, you should see that the images are there but the path is invalid.

|||

can you explain me that how i can set this path and shows the images through datagrid, My acutall path is in the root directory and name of the folder is "upload". and i use this script to get files

do while SDR.Read()

imageList.Add(Page.ResolveUrl("~/upload/") + SDR("event_pic").ToString())

Loop

|||

If you want to display only the pictures, I would recommend you to use a Repeater instead, as it is more logical.

See this tutorial:http://msconline.maconstate.edu/tutorials/ASPNET20/ASPNET08/aspnet08-01.aspx

Also, see this previous post:Using a repeater to display images side by side?

|||

hmmmm, My problem is still remain actually i am a newly person in asp.net and so thats why i have a some problem, suppose if i use repeater but how i can display the images because my database hold only the names of images and the images are in a seperate folder. Is it possible to write a short script for me I shall be very thankful to you.

|||

This is another way to do it, may not be the best, but it works.

<asp:GridViewrunat="server"ID="GridView1"AutoGenerateColumns="false">
<Columns>
<asp:TemplateField>
<ItemTemplate>
<tableid="tt"border="1"cellpadding="5"cellspacing="0">
<tr>
<tdwidth="210">
<%# GetImage(Eval("imagename").ToString()) %>
</td>
</tr>
</table>
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>

Then inside the codebehind

Sub DataBindPhotos()

Dim mConAsNew SqlConnection("server=london-home; Database=tony; uid=rashid2; pwd=test;")

Dim mComAsNew SqlCommand("select * from event_pic where event_sub_id='5' ", mCon)

Dim myAdapAsNew SqlDataAdapterDim myTableAsNew DataTable

myAdap.SelectCommand = mCom

myAdap.Fill(myTable)

GridView1.DataSource = myTable

GridView1.DataBind()

End Sub

Function GetImage(ByVal ImageNameAsString)AsString

Return"<img src=path/to/your/pictures/" + ImageName +" border=0 />"

EndFunction

Get the Names for different levels in a table

hi

I've a table with coln names

ID
Name
ParentID
Level

I've list with different levels

say

ex.

the Data is:-

ID Name ParentID Level
1 Root null 1
2 Trunk 1 2
3 Branch 2 3
4 Leaf 3 4
5 Stem 3 4

How to write the query for getting the Names for different levels for corresponding ParentID....

Output should be like:-

Leaf Branch Trunk Root

Stem Branch Trunk Root

I think you may want to check out hierarchical queries in Books Online. Here is a link with a good example I think you can adjust. http://vyaskn.tripod.com/hierarchies_in_sql_server_databases.htm

Get the Names for different levels in a table

hi

I've a table with coln names

ID
Name
ParentID
Level

I've list with different levels

say

ex.

the Data is:-

ID Name ParentID Level
1 Root null 1
2 Trunk 1 2
3 Branch 2 3
4 Leaf 3 4
5 Stem 3 4

How to write the query for getting the Names for different levels for corresponding ParentID....

Output should be like:-

Leaf Branch Trunk Root

Stem Branch Trunk Root

I think you may want to check out hierarchical queries in Books Online. Here is a link with a good example I think you can adjust. http://vyaskn.tripod.com/hierarchies_in_sql_server_databases.htm

Get the name of all user tables in a database

I want to have a stored procedures which when I pass it the name of a
database it will return all the names of the user tables. I have tried
CREATE PROCEDURE sp_gettables
@.dbname char
AS
EXEC sp_tables @.table_qualifier = "' + @.dbname + '", @.table_type =
"'Table'"
it won't do it as it can only work in its own context. I have also tried
using the use command with a database name as a parameter to point it at the
database. It won't let me do that either. Any ideas, Regards.
How about this?
SELECT TABLE_SCHEMA, TABLE_NAME=20
FROM INFORMATION_SCHEMA.TABLES=20
WHERE TABLE_TYPE =3D 'BASE TABLE'
--=20
Keith
"Chris Kennedy" <nospam@.nospam.co.uk> wrote in message =
news:%23cDtEaoNEHA.1312@.TK2MSFTNGP12.phx.gbl...
> I want to have a stored procedures which when I pass it the name of a
> database it will return all the names of the user tables. I have tried
>=20
> CREATE PROCEDURE sp_gettables
> @.dbname char
> AS
> EXEC sp_tables @.table_qualifier =3D "' + @.dbname + '", @.table_type =
=3D
> "'Table'"
>=20
> it won't do it as it can only work in its own context. I have also =
tried
> using the use command with a database name as a parameter to point it =
at the
> database. It won't let me do that either. Any ideas, Regards.
>=20
>
|||On Mon, 10 May 2004 12:56:58 +0100, Chris Kennedy wrote:

>I want to have a stored procedures which when I pass it the name of a
>database it will return all the names of the user tables. I have tried
>CREATE PROCEDURE sp_gettables
>@.dbname char
>AS
>EXEC sp_tables @.table_qualifier = "' + @.dbname + '", @.table_type =
>"'Table'"
>it won't do it as it can only work in its own context. I have also tried
>using the use command with a database name as a parameter to point it at the
>database. It won't let me do that either. Any ideas, Regards.
>
Hi Chris,
First, it's better not to prefix your stored procedures with sp_. This
prefix has a special meaning to SQL Server, possibly causing unwanted
effects.
Second, datatype char defaults to char(1). Unless your database names
are only one letter long, this will fail. Use nvarchar(128) or sysname
instead.
Third, it is generally preferred to query the INFORMATION_SCHEMA views
instead of the system tables or stored procedures. These views are
ANSI-standard, making your code more portable.
If you want to use sp_tables, use dynamic SQL to concatenate a USE
command and the EXEC sp_tables command. If you prefer to use
INFORMATION_SCHEMA, use the query below (that also uses dynamic SQL).
CREATE PROCEDURE gettables
@.dbname sysname
AS
execute ('select * from ' + @.dbname + '.INFORMATION_SCHEMA.TABLES'
+ ' where TABLE_CATALOG = ''' + @.dbname + '''')
go
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

Get the name of all user tables in a database

I want to have a stored procedures which when I pass it the name of a
database it will return all the names of the user tables. I have tried
CREATE PROCEDURE sp_gettables
@.dbname char
AS
EXEC sp_tables @.table_qualifier = "' + @.dbname + '", @.table_type = "'Table'"
it won't do it as it can only work in its own context. I have also tried
using the use command with a database name as a parameter to point it at the
database. It won't let me do that either. Any ideas, Regards.How about this?
SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE =3D 'BASE TABLE'
-- Keith
"Chris Kennedy" <nospam@.nospam.co.uk> wrote in message =news:%23cDtEaoNEHA.1312@.TK2MSFTNGP12.phx.gbl...
> I want to have a stored procedures which when I pass it the name of a
> database it will return all the names of the user tables. I have tried
> > CREATE PROCEDURE sp_gettables
> @.dbname char
> AS
> EXEC sp_tables @.table_qualifier =3D "' + @.dbname + '", @.table_type ==3D
> "'Table'"
> > it won't do it as it can only work in its own context. I have also =tried
> using the use command with a database name as a parameter to point it =at the
> database. It won't let me do that either. Any ideas, Regards.
> >|||On Mon, 10 May 2004 12:56:58 +0100, Chris Kennedy wrote:
>I want to have a stored procedures which when I pass it the name of a
>database it will return all the names of the user tables. I have tried
>CREATE PROCEDURE sp_gettables
>@.dbname char
>AS
>EXEC sp_tables @.table_qualifier = "' + @.dbname + '", @.table_type =>"'Table'"
>it won't do it as it can only work in its own context. I have also tried
>using the use command with a database name as a parameter to point it at the
>database. It won't let me do that either. Any ideas, Regards.
>
Hi Chris,
First, it's better not to prefix your stored procedures with sp_. This
prefix has a special meaning to SQL Server, possibly causing unwanted
effects.
Second, datatype char defaults to char(1). Unless your database names
are only one letter long, this will fail. Use nvarchar(128) or sysname
instead.
Third, it is generally preferred to query the INFORMATION_SCHEMA views
instead of the system tables or stored procedures. These views are
ANSI-standard, making your code more portable.
If you want to use sp_tables, use dynamic SQL to concatenate a USE
command and the EXEC sp_tables command. If you prefer to use
INFORMATION_SCHEMA, use the query below (that also uses dynamic SQL).
CREATE PROCEDURE gettables
@.dbname sysname
AS
execute ('select * from ' + @.dbname + '.INFORMATION_SCHEMA.TABLES'
+ ' where TABLE_CATALOG = ''' + @.dbname + '''')
go
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Get the name of all user tables in a database

I want to have a stored procedures which when I pass it the name of a
database it will return all the names of the user tables. I have tried
CREATE PROCEDURE sp_gettables
@.dbname char
AS
EXEC sp_tables @.table_qualifier = "' + @.dbname + '", @.table_type =
"'Table'"
it won't do it as it can only work in its own context. I have also tried
using the use command with a database name as a parameter to point it at the
database. It won't let me do that either. Any ideas, Regards.How about this?
SELECT TABLE_SCHEMA, TABLE_NAME=20
FROM INFORMATION_SCHEMA.TABLES=20
WHERE TABLE_TYPE =3D 'BASE TABLE'
--=20
Keith
"Chris Kennedy" <nospam@.nospam.co.uk> wrote in message =
news:%23cDtEaoNEHA.1312@.TK2MSFTNGP12.phx.gbl...
> I want to have a stored procedures which when I pass it the name of a
> database it will return all the names of the user tables. I have tried
>=20
> CREATE PROCEDURE sp_gettables
> @.dbname char
> AS
> EXEC sp_tables @.table_qualifier =3D "' + @.dbname + '", @.table_type =
=3D
> "'Table'"
>=20
> it won't do it as it can only work in its own context. I have also =
tried
> using the use command with a database name as a parameter to point it =
at the
> database. It won't let me do that either. Any ideas, Regards.
>=20
>|||On Mon, 10 May 2004 12:56:58 +0100, Chris Kennedy wrote:

>I want to have a stored procedures which when I pass it the name of a
>database it will return all the names of the user tables. I have tried
>CREATE PROCEDURE sp_gettables
>@.dbname char
>AS
>EXEC sp_tables @.table_qualifier = "' + @.dbname + '", @.table_type =
>"'Table'"
>it won't do it as it can only work in its own context. I have also tried
>using the use command with a database name as a parameter to point it at th
e
>database. It won't let me do that either. Any ideas, Regards.
>
Hi Chris,
First, it's better not to prefix your stored procedures with sp_. This
prefix has a special meaning to SQL Server, possibly causing unwanted
effects.
Second, datatype char defaults to char(1). Unless your database names
are only one letter long, this will fail. Use nvarchar(128) or sysname
instead.
Third, it is generally preferred to query the INFORMATION_SCHEMA views
instead of the system tables or stored procedures. These views are
ANSI-standard, making your code more portable.
If you want to use sp_tables, use dynamic SQL to concatenate a USE
command and the EXEC sp_tables command. If you prefer to use
INFORMATION_SCHEMA, use the query below (that also uses dynamic SQL).
CREATE PROCEDURE gettables
@.dbname sysname
AS
execute ('select * from ' + @.dbname + '.INFORMATION_SCHEMA.TABLES'
+ ' where TABLE_CATALOG = ''' + @.dbname + '''')
go
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Get table_names and column_names

I want to create a function(procedure) which find all my table_names and (column names in those tables) in my database. Do anyone know of a existing function that retrives these names?You can use the INFORMATION_SCHEMA.Columns view for this information.

SELECT * FROM INFORMATION_SCHEMA.Columns

There is also a view called Tables which might be of interest to you.

Terri

Get Stored Procedures parameter names and types...

Sorry if I haven't choose appropriate forum for this question.

I have MSSQL05 beta. I know how to list all stored procedures in selected database (everything is in localhost). I need to list parameter names and types for selected stored procedure(s).
How can I do that or anything that can return parameter names and types?

It's windows application.
You should move to a non-beta version as you are breaking the EULA with using the old version, but anyway, the parameters can be views with the

INFORMATION_SCHEMA.Parameters

view.

SELECT *
FROM INFORMATION_SCHEMA.Parameters

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
|||I will move to non-beta.

Thanks for the reply. This is what I wanted.

Get server\instance name(s) using Physical node name

All:
Short of breaking out the cluster administator tool (GUI), given a
serversnetbios or DNS name, how can I deduce the SQL server names (and
instances) that may be present on that server. Ideally, I could pass
something into a VBSCRIPT function, and get the desired information,
but at this point, even just passing something at the command line that
is parsable would be useful.
Long version of the story is I have a list of 250 servers, but the
physical server name does not always map well to the SQL name,
especailly when instances are involved.
Does this make sense?
thanks,
d.
Look up the SQM-DMO method ListAvailableSQLServers in BOL. You can take
that and filter by InstanceName and ServiceName properties.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
<google@.dcbarry.com> wrote in message
news:1141177148.783918.95800@.v46g2000cwv.googlegro ups.com...
> All:
> Short of breaking out the cluster administator tool (GUI), given a
> serversnetbios or DNS name, how can I deduce the SQL server names (and
> instances) that may be present on that server. Ideally, I could pass
> something into a VBSCRIPT function, and get the desired information,
> but at this point, even just passing something at the command line that
> is parsable would be useful.
>
> Long version of the story is I have a list of 250 servers, but the
> physical server name does not always map well to the SQL name,
> especailly when instances are involved.
>
> Does this make sense?
> thanks,
> d.
>
|||You may find SQLPing.exe by Chip Andrews perfect for this. It talks to UDP
1434 or SQL Browser, but you only need to supply a NetBIOS name, a DNS name,
or even an IP address. It'll return all the SQL Server instance names along
with a few other things. This is a very simple program with C# source code
you can compile yourself.
Google SQLPing.exe, and you'll find it.
Linchi
"google@.dcbarry.com" wrote:

> All:
> Short of breaking out the cluster administator tool (GUI), given a
> serversnetbios or DNS name, how can I deduce the SQL server names (and
> instances) that may be present on that server. Ideally, I could pass
> something into a VBSCRIPT function, and get the desired information,
> but at this point, even just passing something at the command line that
> is parsable would be useful.
>
> Long version of the story is I have a list of 250 servers, but the
> physical server name does not always map well to the SQL name,
> especailly when instances are involved.
>
> Does this make sense?
> thanks,
> d.
>

Get percentage with variation of field values (country names)

Any help here would be greatly appreciated...

Unfortunately, data wasn't filtered prior to getting inserted into this table. Now I am stuck with cleaning it up. I have thought about writing a query to update all the values, but there are just too many variations, including spelling mistakes, so I've ruled that out as a possible solution.
I have a table which has a Country field but the values per record vary. For example US, U.S., USA, United States, UK, United Kingdom, Canada, Can, etc. I'm trying to find the percent of records per country.

Sample table data: mytable
Id Name Country
1 John US
2 James UK
3 Jane United States
4 Mary Canada
5 Jack U.S.
6 Tony United Kingdom
7 Jeff US
8 Tom Canada
9 Beth UK
10 Mark USA
I would like to show
US: 50% --> (includes any variation of US ncluding US, U.S., USA, United States)
UK: 30%
CAN: 20%
I've made several attempts myself with no luck. Thanks in advance.

You have to clean the country list first.

I would do it by retreving distinct country list and update the table for this column mannually( I mean separate updates). For example,

UPDATE mytableSET COUNTRY='USA'

WHERE Country='US'OR Country='U.S.'OR Country='United States'

These three USA names are from your sample data. This OR list will be long if you include all (mis)spellings you can find for the USA from your dirty data source.

After you have clean data, you can do something like this:

SELECT COUNTRY,count(COUNTRY)as cCount,(

CAST(count(COUNTRY)ASfloat)/CAST((SELECTcount(*)FROM countries$) ASfloat)*100)as countryPercent

FROM mytable

GROUPBY country

|||I figured the data would have to be cleaned... thanks for help with the second query, much appreciated... great help in this forum.