SQL CE 动态查询 [英] SQL CE Dynamic Query

问题描述

我在 SQL CE 3.5 中的一个数据库表有一个主键.我想更改主键中的列以将其从 nvarchar(7) 扩展为 nvarchar(20).

One of my database tables in SQL CE 3.5 has a primary key. I want to alter the column in the primary key to expand it from an nvarchar(7) to nvarchar(20).

我是否绝对需要删除主键才能实现这一点?

Do I absolutely need to drop the primary key to achieve this?

如果我不知道主键的名称，如何使用脚本删除主键?

How can I drop the primary key with a script if I do not know the name of the primary key?

例如名称为 PK__sign_type__000000000000228E，因此它是动态生成的.

The name is PK__sign_type__000000000000228E for example so it has been generated dynamically.

我的客户端机器上有 200 或 300 个这样的数据库，因此需要一个通用脚本，因为它们的主键名称各不相同.

I have 200 or 300 of these databases on our clients machines and a generic script is necessary as they will all have different names for this primary key.

推荐答案

首先查询 INFORMATION_SCHEMA.COLUMNS 看是否需要更改:

First query INFORMATION_SCHEMA.COLUMNS to see if the change is required:

SELECT CHARACTER_MAXIMUM_LENGTH FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Shippers' 
AND COLUMN_NAME = 'Company Name'

然后像这样从表中获取约束名称:

Then get the constraint name from the table like this:

SELECT DISTINCT c.CONSTRAINT_NAME 
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS c INNER JOIN 
INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS u ON c.CONSTRAINT_NAME = u.CONSTRAINT_NAME AND      u.TABLE_NAME = c.TABLE_NAME
where c.CONSTRAINT_TYPE = 'PRIMARY KEY' 
AND c.TABLE_NAME = 'Shippers'

然后您可以构造主键删除并更改列.

Then you can construct the primary key drop and alter the column.

记得在重新创建时给主键一个正确的名字.

Remember to give the primary key a proper name when you recreate.

这篇关于SQL CE 动态查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！