Get all source tables indexes from SQL Server query

[Guest]

Hi and goodday,

We have the following table on a SQL Server 2005:

CREATE TABLE [dbo].[CourseItems](
[CourseItemID] [int] IDENTITY(1,1) NOT NULL,
[CourseItem] [varchar](100) NOT NULL,
[CourseID] [int] NULL,
CONSTRAINT [CourseItems_PK] PRIMARY KEY CLUSTERED
(
[CourseItemID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [cstCI] UNIQUE NONCLUSTERED
(
[CourseItem] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]


Using a SQLDataAdapter to create a DataTable with FillSchema you're supposed
to get the datatable with the schema information.

But the index "cstCI" is missing and I do need that information. Actually I
need to get the index from a View based on this Table.

Any hints or clues are welcome,

Thands in advance,

Herman Berger.

 

[Andrea Caldarone]

Hi and goodday,

We have the following table on a SQL Server 2005:

CREATE TABLE [dbo].[CourseItems](
[CourseItemID] [int] IDENTITY(1,1) NOT NULL,
[CourseItem] [varchar](100) NOT NULL,
[CourseID] [int] NULL,
CONSTRAINT [CourseItems_PK] PRIMARY KEY CLUSTERED
(
[CourseItemID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY], CONSTRAINT [cstCI] UNIQUE NONCLUSTERED
(
[CourseItem] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY] ) ON [PRIMARY]


Using a SQLDataAdapter to create a DataTable with FillSchema you're
supposed to get the datatable with the schema information.

But the index "cstCI" is missing and I do need that information.
Actually I need to get the index from a View based on this Table.

Any hints or clues are welcome,

Thands in advance,

Herman Berger.

Query the SQL Server:

SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE
TABLE_NAME='CourseItems'

 

[Guest]

Hi Andrea,

Let me elaborate on the issue.

I need the index on my Datatable. We have the following code:

Dim cnn As SqlClient.SqlConnection = New SqlClient.SqlConnection("Data
Source=F1-dell510mh\server2005;Initial Catalog=Kadmin;Integrated
Security=True")
Dim ad As SqlClient.SqlDataAdapter = New SqlClient.SqlDataAdapter("SELECT *
FROM vwCourseItems", cnn)
Dim da As DataSet = New DataSet
ad.FillSchema(da, SchemaType.Source, "vwCourseItems")

One of the tables in vwCourseItems is the for mentioned table CourseItems
with a primary key and a unique index (cstCI).
Checking da.Tables(0), we see that the da.Tables(0).Columns(1).Unique
property is false. It should be True. ADO.net doesn't pick up on the unique
index. But we need it on our DataTable.

We have a work around in which we create the View with schemabinding and put
an index on the view. For this we need the Smo namespace.

Isn't there really a way via ADO.net to get the indexes from the view?

Kind regards,

Herman Berger.

Hi and goodday,

We have the following table on a SQL Server 2005:

CREATE TABLE [dbo].[CourseItems](
[CourseItemID] [int] IDENTITY(1,1) NOT NULL,
[CourseItem] [varchar](100) NOT NULL,
[CourseID] [int] NULL,
CONSTRAINT [CourseItems_PK] PRIMARY KEY CLUSTERED
(
[CourseItemID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY], CONSTRAINT [cstCI] UNIQUE NONCLUSTERED
(
[CourseItem] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY] ) ON [PRIMARY]


Using a SQLDataAdapter to create a DataTable with FillSchema you're
supposed to get the datatable with the schema information.

But the index "cstCI" is missing and I do need that information.
Actually I need to get the index from a View based on this Table.

Any hints or clues are welcome,

Thands in advance,

Herman Berger.

Query the SQL Server:

SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE
TABLE_NAME='CourseItems'