DIFF Objects

Does anybody know of a tool or script that will tell the difference between
two tables including objects such as indexes?Give Red Gate SQLCompare
(http://www.red-gate.com/products/SQL_Compare/index.htm) a try
Denis the SQL Menace
http://sqlservercode.blogspot.com/
morphius wrote:
> Does anybody know of a tool or script that will tell the difference between
> two tables including objects such as indexes?

Diff b/w executing as Stored Procedure and Script

Hi All,
I have a peculiar problem with SQL2000. When i execute a Stored procedure in Demo & Production i get different outputs. But i copied the business logistics from the Sp and executed as a script in both the servers. Now Both the records are same.

I WANT TO KNOW " WHETHER THERE IS ANY DIFFERENCE IN EXECUTION METHODOLOGY BETWEEN STORED PROCEDURE AND QUERY".

NOTE: My stored procedure has 14 executable scripts. Upto 10 scripts no date comparisons were made. But at the 11th script the records differ.

I DOUBT WHETHER THERE WILL BE ANY DATE RELATED ISSUE WHEN EXECUTING AS STORED PROCEDURE AND SCRIPT

Hi,

There is no date related issue for a stored procedure to execute as for as I know. the diffrence between the stored proceudre and the script is the execution plan and the compiling time. remember some time in stored procedure might return the cached data. If possible try to start and stop the DB and then try to execute in both they day.

Might be some thing wrong in the quries in SP and the script which youa re running.

Take a look.

Mohan

|||

Hi Mohan,

Thanks for your reply. There is no chance for error in the script. How i say this bcoz " When i am running the script and the queries in the demo server i get the same result. So only i am very sure about that. Anyway you have given me a new Information. I shall take the necessary steps.

The DIFFERENCE occurs only in PRODUCTION server.

Diagram, script and security ...

Three things:

1 - I attached a database from another computer in the Microsoft SQL Server Management Studio Express tool. It looks like my database diagrams did not follow.At first, it told me that the owner of the database was not set correctly and, hence, diagrams could not be supported. I ran the following query to fix that -> Alter authorization on database::DB_NAME to [MACHINE_NAME\USER]. Now, I can create new diagrams but I would like to see the ones I had before. How can I transfer my database files (.mdf and .ldf) with the diagrams?

2 – I would like to generate a database script that would not only create the table structure but also generate the insert statements to populate the database. When I use the “generate scripts” wizard, I don’t see an option where I could get the insert statements included in the script.Any help would be appreciated.

3 - I would like to read/find a short tutorial on how to set up security for my database. For now, I use the default user/security everywhere and I am sure it is not a good way of doing things. Basically, I would like to make sure I am not leaving a big security hole without having to read an entire book on SQL Server.Where could I get started?

Thanks a lot,

Dom.

1/ the diagrams should have come across with the database as they are stored in special tables, was this a SQL 2000 db that was upgraded by any chance?

2/ There is no way to generate insert statements in the UI today, there are some samples out in the community that can do it through.

3/ Have you read books on line?

|||

1 - No this is not a database upgrade, a SQL Server 2005 Express Edition to SQL Server 2005 Express Edition

2 - This is sad, there should be something to generate insert statements.

3 - Do you have any good ones to suggest?

thanks.

|||

Check out Securing your SQL Server Express Edition Server, it's from beta, but all the prinicples apply. You can also read the various pages of the BOL linked to from the Security Consideration for SQL Server topic.

Regards,

Mike Wachal
SQL Express team

-
Mark the best posts as Answers!

Development Vs Enterprise | Performance

We're trying to work out why the same SQL Script takes much longer to run within the Enterprise environment compared to the Development?

What makes matters worse is the development version is on a standard desktop (P4 1.8 256MB RAM) compared to the Enterprise version which sits on a dual Xeon with 1Gig of RAM.

Is is related to the SQL product or am I missing something here? :confused:What level of user activity is occuring on your "Enterprise" (I assume you mean "Production") environment?

What does the script do? Heavy inserts/updates/deletes? These could all be affected by other users accessing the same data tables simultaneously.

Does indexing match between the two systems?

Have you run UPDATE STATISTICS on your production environment?

Are the two databases the same size?

Lots of things that could cause this...|||I'm trying to work out why our SQL production server is so slow.
Using Select statements everything is fine but using an Insert statement causes immense slow down.

I created a new database in each of the environments listed below then ran a test script via Query Analyser.

The Desktop running the development edition took just over 1 minute, the server - running the full production app - took nearly 7 minutes.

The script is basic and simply created a table then inserts 100,000 records - I've copied the script below.

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Test_Table]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Test_Table]
GO

CREATE TABLE [dbo].[Test_Table] (
[ID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

DECLARE @.i int
SET @.i=100000
WHILE @.i>0
BEGIN
INSERT INTO Test_Table values (@.i)
SET @.i=@.i-1
END|||You have to make the tests with the same load on both servers, that's the only way to get a value that you can compare between those two...|||There's zero load on either machine as I perform these tests.
Using Task Manager I monitored CPU and Memory usage which remains at a minimum.
Well and truly stuck here. Do you think is could be a configuration issue in SQL? I've checked and both machines are running Service Pack 3a.

Does the developer edition process differently compared to the full Enterprise/Production Edition?|||There's zero load on either machine as I perform these tests.
...

So you have put the production server "offline" during testing?
...and you are 100% sure that nobody is accessing the server during the performance test!?

Does the developer edition process differently compared to the full Enterprise/Production Edition?
I don't think that there should be big differences, and if there were they should be the other way round...|||We have resolved this issue - thanks to all those who helped.
The production servers are RAID enabled, and we've had to fit a battery to enable write caching. So ultimatley is was an I/O issue but hardware related.

Developing with Enterprise tools, deploying without them

I have designed my database using Sql 2000 Developer Edition, but I need to apply that design to MSDE. I can run the script on a database using MSDE through Enterprise manager on my machine and, using Enterprise manager again, back the database up. How do I restore that backup to a MSDE database on another machine without Enterprise tools?

In other words, I need to install MSDE on another machine and restore a backed up database design to it, without Enterprise tools on the host machine.

Also: If I want to use exactly the same database I have developed using Enterprise tools packaged with Sql 2000 Developer Edition, how may I back 'that' database up so that it may be restored to a MSDE database or replicate the database in question to an MSDE client?

Thank you,
John SpurlinYou can execute a script or query using OSQL from the command line. See the follwoing MSDN article for more details:

osql Utility

Hope this helps

Regards

Wayne Phipps

Determining single-column candidate keys

This script will tell you all of the unique columns (except BLOBs) in
all of the tables in a database.
I made a post last night with some VBA code for exporting data from
Access. I've been given a 2 GB Access database with scores of tables,
some of them having hundreds of columns, millions of rows, and no
constraints. I also know very little about the underlying data. After
getting the data into SQL Server, I wanted to learn more about the
nature of the data. The first thing I wanted to find was which columns
could be single-column candidate keys.
One of the issues I ran into is that my generated SQL often exceeded
4000 characters, so I needed to come up with a way of executing more
than 4000 characters. Also, y'all'll notice that I'm using cursors
quite a bit here. These cursors only iterate through a few rows. The
heavy stuff is pretty efficient SQL.
-Alan
SET NOCOUNT ON
CREATE TABLE #UniqueColumns
(
TableName sysname
, ColumnName sysname
, IsUnique CHAR(1)
, PRIMARY KEY (TableName, ColumnName)
)
DECLARE @.TableName sysname
, @.ColumnName sysname
DECLARE cTables CURSOR
LOCAL
FAST_FORWARD
FOR
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
CREATE TABLE #SQLFragments
(
RowNumber INT
IDENTITY
, SQL NVARCHAR(4000)
, Type VARCHAR(6)
NOT NULL
CHECK (Type IN ('SELECT', 'UPDATE'))
)
OPEN cTables
WHILE 1 = 1 BEGIN
FETCH NEXT FROM cTables INTO @.TableName
IF @.@.FETCH_STATUS <> 0 BREAK
DECLARE @.SQL NVARCHAR(4000)
, @.SQLUpdateUnique NVARCHAR(4000)
SET @.SQL = N'SELECT '
SET @.SQLUpdateUnique = N''
DECLARE c CURSOR
LOCAL
FAST_FORWARD
FOR
SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.columns
WHERE TABLE_NAME = @.TableName
AND DATA_TYPE NOT IN ('text', 'ntext', 'image')
ORDER BY ORDINAL_POSITION
OPEN c
WHILE 1 = 1 BEGIN
FETCH NEXT FROM c INTO @.ColumnName
IF @.@.FETCH_STATUS <> 0 BREAK
INSERT #UniqueColumns (TableName, ColumnName)
SELECT @.TableName, @.ColumnName
SET @.SQL = @.SQL + '
CASE WHEN COUNT (DISTINCT ' + @.ColumnName + ') = COUNT(*) THEN ''Y''
ELSE ''N'' END ' + @.ColumnName + ','
SET @.SQLUpdateUnique = @.SQLUpdateUnique + 'UPDATE #UniqueColumns SET
IsUnique = ' + @.ColumnName + ' FROM ##Temp WHERE TableName = ''' +
@.TableName + ''' AND ColumnName = ''' + @.ColumnName + '''
'
IF LEN(@.SQL) > 3000 BEGIN
INSERT #SQLFragments (SQL, Type)
SELECT @.SQL, 'SELECT'
SET @.SQL = ''
END
IF LEN(@.SQLUpdateUnique) > 3000 BEGIN
INSERT #SQLFragments (SQL, Type)
SELECT @.SQLUpdateUnique, 'UPDATE'
SET @.SQLUpdateUnique = ''
END
END
CLOSE c
DEALLOCATE c
SET @.SQL = LEFT(@.SQL, LEN(@.SQL) - 1) + ' INTO ##Temp FROM ' +
@.TableName
INSERT #SQLFragments (SQL, Type)
SELECT @.SQL, 'SELECT'
INSERT #SQLFragments (SQL, Type)
SELECT @.SQLUpdateUnique, 'UPDATE'
DECLARE @.FragmentCount INT
SELECT @.FragmentCount = COUNT(*) FROM #SQLFragments WHERE Type =
'SELECT'
DECLARE @.SQLEXEC NVARCHAR(4000)
, @.Fragment NVARCHAR(4000)
, @.RowNumber VARCHAR(10)
SET @.SQL = N''
SET @.SQLEXEC = N'EXEC ('
DECLARE cFragments CURSOR
LOCAL
FAST_FORWARD
FOR
SELECT SQL, RowNumber
FROM #SQLFragments
WHERE Type = 'SELECT'
ORDER BY RowNumber
OPEN cFragments
WHILE 1 = 1 BEGIN
FETCH NEXT FROM cFragments INTO @.Fragment, @.RowNumber
IF @.@.FETCH_STATUS <> 0 BREAK
SET @.SQL = @.SQL + N'DECLARE @.SQL' + @.RowNumber + N' NVARCHAR(4000)
SELECT @.SQL' + @.RowNumber + N' = SQL FROM #SQLFragments WHERE RowNumber
= ' + @.RowNumber + N'
'
SET @.SQLEXEC = @.SQLEXEC + N' @.SQL' + @.RowNumber + N' +'
END
CLOSE cFragments
DEALLOCATE cFragments
SET @.SQLEXEC = LEFT(@.SQLEXEC, LEN(@.SQLEXEC) - 1) + ')'
SET @.SQL = @.SQL + N' ' + @.SQLEXEC
EXEC sp_executesql @.SQL
SET @.SQL = N''
SET @.SQLEXEC = N'EXEC ('
DECLARE cFragments CURSOR
LOCAL
FAST_FORWARD
FOR
SELECT SQL, RowNumber
FROM #SQLFragments
WHERE Type = 'UPDATE'
ORDER BY RowNumber
OPEN cFragments
WHILE 1 = 1 BEGIN
FETCH NEXT FROM cFragments INTO @.Fragment, @.RowNumber
IF @.@.FETCH_STATUS <> 0 BREAK
SET @.SQL = @.SQL + N'DECLARE @.SQL' + @.RowNumber + N' NVARCHAR(4000)
SELECT @.SQL' + @.RowNumber + N' = SQL FROM #SQLFragments WHERE RowNumber
= ' + @.RowNumber + N'
'
SET @.SQLEXEC = @.SQLEXEC + N' @.SQL' + @.RowNumber + N' +'
END
CLOSE cFragments
DEALLOCATE cFragments
SET @.SQL = @.SQL + N' ' + LEFT(@.SQLEXEC, LEN(@.SQLEXEC) - 1) + N')'
EXEC sp_executesql @.SQL
DELETE #SQLFragments
DROP TABLE ##Temp
END
CLOSE cTables
DEALLOCATE cTables
DELETE #UniqueColumns WHERE IsUnique = 'N'
SELECT * FROM #UniqueColumns
DROP TABLE #UniqueColumns
DROP TABLE #SQLFragmentsYou can analyze the data and make assumptions about keys and relationships,
but can the source of the data also provide a data dictionary?
"Alan Samet" <alansamet@.gmail.com> wrote in message
news:1141922084.124478.54740@.j52g2000cwj.googlegroups.com...
> This script will tell you all of the unique columns (except BLOBs) in
> all of the tables in a database.
> I made a post last night with some VBA code for exporting data from
> Access. I've been given a 2 GB Access database with scores of tables,
> some of them having hundreds of columns, millions of rows, and no
> constraints. I also know very little about the underlying data. After
> getting the data into SQL Server, I wanted to learn more about the
> nature of the data. The first thing I wanted to find was which columns
> could be single-column candidate keys.
> One of the issues I ran into is that my generated SQL often exceeded
> 4000 characters, so I needed to come up with a way of executing more
> than 4000 characters. Also, y'all'll notice that I'm using cursors
> quite a bit here. These cursors only iterate through a few rows. The
> heavy stuff is pretty efficient SQL.
> -Alan
> SET NOCOUNT ON
> CREATE TABLE #UniqueColumns
> (
> TableName sysname
> , ColumnName sysname
> , IsUnique CHAR(1)
> , PRIMARY KEY (TableName, ColumnName)
> )
> DECLARE @.TableName sysname
> , @.ColumnName sysname
> DECLARE cTables CURSOR
> LOCAL
> FAST_FORWARD
> FOR
> SELECT TABLE_NAME
> FROM INFORMATION_SCHEMA.TABLES
> CREATE TABLE #SQLFragments
> (
> RowNumber INT
> IDENTITY
> , SQL NVARCHAR(4000)
> , Type VARCHAR(6)
> NOT NULL
> CHECK (Type IN ('SELECT', 'UPDATE'))
> )
> OPEN cTables
> WHILE 1 = 1 BEGIN
> FETCH NEXT FROM cTables INTO @.TableName
> IF @.@.FETCH_STATUS <> 0 BREAK
> DECLARE @.SQL NVARCHAR(4000)
> , @.SQLUpdateUnique NVARCHAR(4000)
> SET @.SQL = N'SELECT '
> SET @.SQLUpdateUnique = N''
> DECLARE c CURSOR
> LOCAL
> FAST_FORWARD
> FOR
> SELECT COLUMN_NAME
> FROM INFORMATION_SCHEMA.columns
> WHERE TABLE_NAME = @.TableName
> AND DATA_TYPE NOT IN ('text', 'ntext', 'image')
> ORDER BY ORDINAL_POSITION
> OPEN c
> WHILE 1 = 1 BEGIN
> FETCH NEXT FROM c INTO @.ColumnName
> IF @.@.FETCH_STATUS <> 0 BREAK
> INSERT #UniqueColumns (TableName, ColumnName)
> SELECT @.TableName, @.ColumnName
> SET @.SQL = @.SQL + '
> CASE WHEN COUNT (DISTINCT ' + @.ColumnName + ') = COUNT(*) THEN ''Y''
> ELSE ''N'' END ' + @.ColumnName + ','
> SET @.SQLUpdateUnique = @.SQLUpdateUnique + 'UPDATE #UniqueColumns SET
> IsUnique = ' + @.ColumnName + ' FROM ##Temp WHERE TableName = ''' +
> @.TableName + ''' AND ColumnName = ''' + @.ColumnName + '''
> '
> IF LEN(@.SQL) > 3000 BEGIN
> INSERT #SQLFragments (SQL, Type)
> SELECT @.SQL, 'SELECT'
> SET @.SQL = ''
> END
> IF LEN(@.SQLUpdateUnique) > 3000 BEGIN
> INSERT #SQLFragments (SQL, Type)
> SELECT @.SQLUpdateUnique, 'UPDATE'
> SET @.SQLUpdateUnique = ''
> END
> END
> CLOSE c
> DEALLOCATE c
> SET @.SQL = LEFT(@.SQL, LEN(@.SQL) - 1) + ' INTO ##Temp FROM ' +
> @.TableName
> INSERT #SQLFragments (SQL, Type)
> SELECT @.SQL, 'SELECT'
> INSERT #SQLFragments (SQL, Type)
> SELECT @.SQLUpdateUnique, 'UPDATE'
> DECLARE @.FragmentCount INT
> SELECT @.FragmentCount = COUNT(*) FROM #SQLFragments WHERE Type =
> 'SELECT'
> DECLARE @.SQLEXEC NVARCHAR(4000)
> , @.Fragment NVARCHAR(4000)
> , @.RowNumber VARCHAR(10)
> SET @.SQL = N''
> SET @.SQLEXEC = N'EXEC ('
> DECLARE cFragments CURSOR
> LOCAL
> FAST_FORWARD
> FOR
> SELECT SQL, RowNumber
> FROM #SQLFragments
> WHERE Type = 'SELECT'
> ORDER BY RowNumber
> OPEN cFragments
> WHILE 1 = 1 BEGIN
> FETCH NEXT FROM cFragments INTO @.Fragment, @.RowNumber
> IF @.@.FETCH_STATUS <> 0 BREAK
> SET @.SQL = @.SQL + N'DECLARE @.SQL' + @.RowNumber + N' NVARCHAR(4000)
> SELECT @.SQL' + @.RowNumber + N' = SQL FROM #SQLFragments WHERE RowNumber
> = ' + @.RowNumber + N'
> '
> SET @.SQLEXEC = @.SQLEXEC + N' @.SQL' + @.RowNumber + N' +'
> END
> CLOSE cFragments
> DEALLOCATE cFragments
> SET @.SQLEXEC = LEFT(@.SQLEXEC, LEN(@.SQLEXEC) - 1) + ')'
> SET @.SQL = @.SQL + N' ' + @.SQLEXEC
> EXEC sp_executesql @.SQL
> SET @.SQL = N''
> SET @.SQLEXEC = N'EXEC ('
> DECLARE cFragments CURSOR
> LOCAL
> FAST_FORWARD
> FOR
> SELECT SQL, RowNumber
> FROM #SQLFragments
> WHERE Type = 'UPDATE'
> ORDER BY RowNumber
> OPEN cFragments
> WHILE 1 = 1 BEGIN
> FETCH NEXT FROM cFragments INTO @.Fragment, @.RowNumber
> IF @.@.FETCH_STATUS <> 0 BREAK
> SET @.SQL = @.SQL + N'DECLARE @.SQL' + @.RowNumber + N' NVARCHAR(4000)
> SELECT @.SQL' + @.RowNumber + N' = SQL FROM #SQLFragments WHERE RowNumber
> = ' + @.RowNumber + N'
> '
> SET @.SQLEXEC = @.SQLEXEC + N' @.SQL' + @.RowNumber + N' +'
> END
> CLOSE cFragments
> DEALLOCATE cFragments
> SET @.SQL = @.SQL + N' ' + LEFT(@.SQLEXEC, LEN(@.SQLEXEC) - 1) + N')'
> EXEC sp_executesql @.SQL
> DELETE #SQLFragments
> DROP TABLE ##Temp
> END
> CLOSE cTables
> DEALLOCATE cTables
> DELETE #UniqueColumns WHERE IsUnique = 'N'
> SELECT * FROM #UniqueColumns
> DROP TABLE #UniqueColumns
> DROP TABLE #SQLFragments
>|||I can't get a data dictionary. This data is only in intermediate form
anyways. I'm writing my own database for the front-end application, but
I need some of the data that's in this database. A lot of it's not
normalized, et cetera. Once I know exactly what I need for my
application, the database I write will be properly constrained. The
purpose of the script was to reduce the amount of time it takes me to
understand the underlying data.
-Alan|||Alan,
I believe it's possible to execute longer strings by combining them and
using EXEC instead of sp_executesql eg:
EXEC( @.select + ' ' + @.from )
However, that should probably telling you there might be another way to do
it. How about capturing your information first?
ie have two tables, one to represent the tables in your Access database and
one to represent the columns ( or attributes Joe )? Something like:
Table: access_tables
at_id
table_name
rows
Table: access_fields
af_id
at_id
field_name
unique values
rows
Run one script which captures the data required, then you can run nice fast
SELECTs on real data:
SELECT *
FROM access_tables t
INNER JOIN access_fields f ON t.at_id = f.at_id
WHERE t.rows = f.unique_values
It's sometimes frowned upon to gather metadata like this in your database
but I see what you're trying to do.
Have fun!
Damien
"Alan Samet" wrote:

> This script will tell you all of the unique columns (except BLOBs) in
> all of the tables in a database.
> I made a post last night with some VBA code for exporting data from
> Access. I've been given a 2 GB Access database with scores of tables,
> some of them having hundreds of columns, millions of rows, and no
> constraints. I also know very little about the underlying data. After
> getting the data into SQL Server, I wanted to learn more about the
> nature of the data. The first thing I wanted to find was which columns
> could be single-column candidate keys.
> One of the issues I ran into is that my generated SQL often exceeded
> 4000 characters, so I needed to come up with a way of executing more
> than 4000 characters. Also, y'all'll notice that I'm using cursors
> quite a bit here. These cursors only iterate through a few rows. The
> heavy stuff is pretty efficient SQL.
> -Alan
> SET NOCOUNT ON
> CREATE TABLE #UniqueColumns
> (
> TableName sysname
> , ColumnName sysname
> , IsUnique CHAR(1)
> , PRIMARY KEY (TableName, ColumnName)
> )
> DECLARE @.TableName sysname
> , @.ColumnName sysname
> DECLARE cTables CURSOR
> LOCAL
> FAST_FORWARD
> FOR
> SELECT TABLE_NAME
> FROM INFORMATION_SCHEMA.TABLES
> CREATE TABLE #SQLFragments
> (
> RowNumber INT
> IDENTITY
> , SQL NVARCHAR(4000)
> , Type VARCHAR(6)
> NOT NULL
> CHECK (Type IN ('SELECT', 'UPDATE'))
> )
> OPEN cTables
> WHILE 1 = 1 BEGIN
> FETCH NEXT FROM cTables INTO @.TableName
> IF @.@.FETCH_STATUS <> 0 BREAK
> DECLARE @.SQL NVARCHAR(4000)
> , @.SQLUpdateUnique NVARCHAR(4000)
> SET @.SQL = N'SELECT '
> SET @.SQLUpdateUnique = N''
> DECLARE c CURSOR
> LOCAL
> FAST_FORWARD
> FOR
> SELECT COLUMN_NAME
> FROM INFORMATION_SCHEMA.columns
> WHERE TABLE_NAME = @.TableName
> AND DATA_TYPE NOT IN ('text', 'ntext', 'image')
> ORDER BY ORDINAL_POSITION
> OPEN c
> WHILE 1 = 1 BEGIN
> FETCH NEXT FROM c INTO @.ColumnName
> IF @.@.FETCH_STATUS <> 0 BREAK
> INSERT #UniqueColumns (TableName, ColumnName)
> SELECT @.TableName, @.ColumnName
> SET @.SQL = @.SQL + '
> CASE WHEN COUNT (DISTINCT ' + @.ColumnName + ') = COUNT(*) THEN ''Y''
> ELSE ''N'' END ' + @.ColumnName + ','
> SET @.SQLUpdateUnique = @.SQLUpdateUnique + 'UPDATE #UniqueColumns SET
> IsUnique = ' + @.ColumnName + ' FROM ##Temp WHERE TableName = ''' +
> @.TableName + ''' AND ColumnName = ''' + @.ColumnName + '''
> '
> IF LEN(@.SQL) > 3000 BEGIN
> INSERT #SQLFragments (SQL, Type)
> SELECT @.SQL, 'SELECT'
> SET @.SQL = ''
> END
> IF LEN(@.SQLUpdateUnique) > 3000 BEGIN
> INSERT #SQLFragments (SQL, Type)
> SELECT @.SQLUpdateUnique, 'UPDATE'
> SET @.SQLUpdateUnique = ''
> END
> END
> CLOSE c
> DEALLOCATE c
> SET @.SQL = LEFT(@.SQL, LEN(@.SQL) - 1) + ' INTO ##Temp FROM ' +
> @.TableName
> INSERT #SQLFragments (SQL, Type)
> SELECT @.SQL, 'SELECT'
> INSERT #SQLFragments (SQL, Type)
> SELECT @.SQLUpdateUnique, 'UPDATE'
> DECLARE @.FragmentCount INT
> SELECT @.FragmentCount = COUNT(*) FROM #SQLFragments WHERE Type =
> 'SELECT'
> DECLARE @.SQLEXEC NVARCHAR(4000)
> , @.Fragment NVARCHAR(4000)
> , @.RowNumber VARCHAR(10)
> SET @.SQL = N''
> SET @.SQLEXEC = N'EXEC ('
> DECLARE cFragments CURSOR
> LOCAL
> FAST_FORWARD
> FOR
> SELECT SQL, RowNumber
> FROM #SQLFragments
> WHERE Type = 'SELECT'
> ORDER BY RowNumber
> OPEN cFragments
> WHILE 1 = 1 BEGIN
> FETCH NEXT FROM cFragments INTO @.Fragment, @.RowNumber
> IF @.@.FETCH_STATUS <> 0 BREAK
> SET @.SQL = @.SQL + N'DECLARE @.SQL' + @.RowNumber + N' NVARCHAR(4000)
> SELECT @.SQL' + @.RowNumber + N' = SQL FROM #SQLFragments WHERE RowNumber
> = ' + @.RowNumber + N'
> '
> SET @.SQLEXEC = @.SQLEXEC + N' @.SQL' + @.RowNumber + N' +'
> END
> CLOSE cFragments
> DEALLOCATE cFragments
> SET @.SQLEXEC = LEFT(@.SQLEXEC, LEN(@.SQLEXEC) - 1) + ')'
> SET @.SQL = @.SQL + N' ' + @.SQLEXEC
> EXEC sp_executesql @.SQL
> SET @.SQL = N''
> SET @.SQLEXEC = N'EXEC ('
> DECLARE cFragments CURSOR
> LOCAL
> FAST_FORWARD
> FOR
> SELECT SQL, RowNumber
> FROM #SQLFragments
> WHERE Type = 'UPDATE'
> ORDER BY RowNumber
> OPEN cFragments
> WHILE 1 = 1 BEGIN
> FETCH NEXT FROM cFragments INTO @.Fragment, @.RowNumber
> IF @.@.FETCH_STATUS <> 0 BREAK
> SET @.SQL = @.SQL + N'DECLARE @.SQL' + @.RowNumber + N' NVARCHAR(4000)
> SELECT @.SQL' + @.RowNumber + N' = SQL FROM #SQLFragments WHERE RowNumber
> = ' + @.RowNumber + N'
> '
> SET @.SQLEXEC = @.SQLEXEC + N' @.SQL' + @.RowNumber + N' +'
> END
> CLOSE cFragments
> DEALLOCATE cFragments
> SET @.SQL = @.SQL + N' ' + LEFT(@.SQLEXEC, LEN(@.SQLEXEC) - 1) + N')'
> EXEC sp_executesql @.SQL
> DELETE #SQLFragments
> DROP TABLE ##Temp
> END
> CLOSE cTables
> DEALLOCATE cTables
> DELETE #UniqueColumns WHERE IsUnique = 'N'
> SELECT * FROM #UniqueColumns
> DROP TABLE #UniqueColumns
> DROP TABLE #SQLFragments
>|||>> .. and one to represent the columns ( or attributes Joe )? <<
LOL! Eitgher, but not "fields", please !
We can avoid the metadata with a little work. Pull out the table and
column names then use a text editor to generate queries with this
template
/*
DROP TABLE Foobar;
CREATE TABLE Foobar
(known_key CHAR(2) NOT NULL PRIMARY KEY,
actual_key CHAR(2) NOT NULL,
non_key CHAR(2) NOT NULL,
non_key_null CHAR(2));
INSERT INTO Foobar VALUES ('K1', 'a', 'x', 'a');
INSERT INTO Foobar VALUES ('K2', 'b', 'x', NULL);
INSERT INTO Foobar VALUES ('K3', 'c', 'x', 'b');
INSERT INTO Foobar VALUES ('K4', 'd', 'y', 'c');
INSERT INTO Foobar VALUES ('K5', 'e', 'y', 'd');
INSERT INTO Foobar VALUES ('K6', 'f', 'y', 'e');
*/
SELECT 'Foobar' AS table_name,
CASE WHEN COUNT(DISTINCT known_key) = COUNT(*)
THEN 'Y' ELSE 'N' END AS known_key,
CASE WHEN COUNT(DISTINCT actual_key) = COUNT(*)
THEN 'Y' ELSE 'N' END AS actual_key,
CASE WHEN COUNT(DISTINCT non_key) = COUNT(*)
THEN 'Y' ELSE 'N' END AS non_key,
CASE WHEN COUNT(DISTINCT non_key_null) = COUNT(*)
THEN 'Y' ELSE 'N' END AS non_key_null
FROM Foobar
These should run faster than a cursor. NULLs and dups will cause
COUNT(*) and COUNT(DISTINCT col) to be unequal. COUNT(*) should be
computed only once, and the optimizer should catch NOT NULL UNIQUE
constraints to short-cut the equality test.
No need for loops.|||Joe, that's almost exactly what I did. The cursors were used for two
things:
building dynamic SQL statements for the select case when count(...)
statements and the update statement to update my result table. I
prioritized execution efficiency of the queries that determined
uniqueness over query generation.
In more detail, the technique I used is as follows.
Here is your table w/ unknown constraints:
CREATE TABLE Foobar
(
Column0 DATATYPE
, Column1 DATATYPE
..
, Column 499 DATATYPE
)
First, I created a table of all table names and column names with a
column to indicate whether the value was unique.
CREATE TABLE #UniqueColumns
(
TableName sysname
, ColumnName sysname
, IsUnique CHAR(1)
, PRIMARY KEY (TableName, ColumnName)
)
Next, for each table I determined which columns were unique. Since I'm
dealing with lots of records, I wanted this to be the most efficient.
I'd say this query did the trick.
SELECT CASE WHEN COUNT(DISTINCT Column0) = COUNT(*) THEN 'Y' ELSE 'N'
END Column0
, CASE WHEN COUNT(DISTINCT Column1) = COUNT(*) THEN 'Y' ELSE 'N' END
Column1
, CASE WHEN COUNT(DISTINCT Column2) = COUNT(*) THEN 'Y' ELSE 'N' END
Column2
... et cetera
INTO ##Temp
FROM TableName
I then updated my table with table and column names:
UPDATE #UniqueColumns SET IsUnique = Column0 FROM ##Temp WHERE
TableName = 'TableName' AND ColumnName = 'Column0'
UPDATE #UniqueColumns SET IsUnique = Column1 FROM ##Temp WHERE
TableName = 'TableName' AND ColumnName = 'Column1'
The only issue here was when my SQL exceeded 4000 characters. So, I
went ahead and created a temporary table to store the fragments. This
is one of the few cases where the IDENTITY keyword is actually useful.
The latter part of the query would take and create a script that looked
like the following and execute it:
DECLARE @.SQL1 NVARCHAR(4000)
SELECT @.SQL1 = SQL FROM #SQLFragments WHERE RowNumber = 1
DECLARE @.SQL2 NVARCHAR(4000)
SELECT @.SQL2 = SQL FROM #SQLFragments WHERE RowNumber = 2
EXEC (@.SQL1 + @.SQL2)
et cetera. This was my dynamic workaround for the 4000 character
limitation. In reality, I doubt anything would gone over 12,000, so I
could've probably gotten away with just a couple variables, but this
was way more fun.
-Alan
--CELKO-- wrote:
> LOL! Eitgher, but not "fields", please !
>
> We can avoid the metadata with a little work. Pull out the table and
> column names then use a text editor to generate queries with this
> template
> /*
> DROP TABLE Foobar;
> CREATE TABLE Foobar
> (known_key CHAR(2) NOT NULL PRIMARY KEY,
> actual_key CHAR(2) NOT NULL,
> non_key CHAR(2) NOT NULL,
> non_key_null CHAR(2));
> INSERT INTO Foobar VALUES ('K1', 'a', 'x', 'a');
> INSERT INTO Foobar VALUES ('K2', 'b', 'x', NULL);
> INSERT INTO Foobar VALUES ('K3', 'c', 'x', 'b');
> INSERT INTO Foobar VALUES ('K4', 'd', 'y', 'c');
> INSERT INTO Foobar VALUES ('K5', 'e', 'y', 'd');
> INSERT INTO Foobar VALUES ('K6', 'f', 'y', 'e');
> */
> SELECT 'Foobar' AS table_name,
> CASE WHEN COUNT(DISTINCT known_key) = COUNT(*)
> THEN 'Y' ELSE 'N' END AS known_key,
> CASE WHEN COUNT(DISTINCT actual_key) = COUNT(*)
> THEN 'Y' ELSE 'N' END AS actual_key,
> CASE WHEN COUNT(DISTINCT non_key) = COUNT(*)
> THEN 'Y' ELSE 'N' END AS non_key,
> CASE WHEN COUNT(DISTINCT non_key_null) = COUNT(*)
> THEN 'Y' ELSE 'N' END AS non_key_null
> FROM Foobar
> These should run faster than a cursor. NULLs and dups will cause
> COUNT(*) and COUNT(DISTINCT col) to be unequal. COUNT(*) should be
> computed only once, and the optimizer should catch NOT NULL UNIQUE
> constraints to short-cut the equality test.
> No need for loops.|||Alan,
Neat stuff!
My simpleminded approach to the same sort of problem follows. It
doesn't use the owner or schema, as it should, and doesn't filter on
datatypes, as it should, but the general idea is clear enough I think.
The first column of the result tells you the table name and the number
of rows, and I scan visually for the candidate keys. On the other
hand, it gives me a complete picture of how many values per column,
and that can be invaluable to sorting out mysterious data.
Roy Harvey
Beacon Falls, CT
SELECT CASE WHEN ORDINAL_POSITION = 1
THEN 'SELECT COUNT(*) as ' + TABLE_NAME + ',' +
char(13) + char(10)
ELSE ''
END +
' COUNT(distinct ' + COLUMN_NAME + ') as ' + COLUMN_NAME
+
CASE WHEN ORDINAL_POSITION <
(select max(ORDINAL_POSITION)
from INFORMATION_SCHEMA.COLUMNS as L
where C.TABLE_NAME = L.TABLE_NAME)
THEN ','
ELSE char(13) + char(10) +
' FROM ' + TABLE_NAME
END
from INFORMATION_SCHEMA.COLUMNS as C
order by TABLE_NAME, ORDINAL_POSITION