sql小问题,就是为了善分啦~

xiangyun_1224 2011-09-15 05:27:51
1.怎么只导出数据库中表的字段及数据类型,不导出数据内容?
2.字段ID为自动增长,删除数据后,怎么设置ID重新从1开始自动增长?
...全文
150 25 打赏 收藏 转发到动态 举报
写回复
用AI写文章
25 条回复
切换为时间正序
请发表友善的回复…
发表回复
xiangyun_1224 2011-09-16
  • 打赏
  • 举报
回复
17楼--geniuswjt 答案可以。执行脚本的时候要在脚本前选择另一个数据库名。但是我还是想用T-sql语言操作,而不是这种sql工具自带的。

有吗?有的话再追加20分结题。
没有就吃个饭来结题。
geniuswjt 2011-09-16
  • 打赏
  • 举报
回复
脚本有了复制下来,到另一个数据库粘贴,执行不就完了。。。汗[Quote=引用 23 楼 xiangyun_1224 的回复:]
17楼--geniuswjt 答案可以。但是==。后面操作从一个数据库到另一个数据库的操作不详细。本人在亲测一哈,要是找到用sql语句能实现的话就准备结题。
[/Quote]
xiangyun_1224 2011-09-16
  • 打赏
  • 举报
回复
17楼--geniuswjt 答案可以。但是==。后面操作从一个数据库到另一个数据库的操作不详细。本人在亲测一哈,要是找到用sql语句能实现的话就准备结题。
geniuswjt 2011-09-16
  • 打赏
  • 举报
回复
杂可能,亲测可行的,你是不是弄错了哦[Quote=引用 21 楼 xiangyun_1224 的回复:]
17楼的会将所有数据导出。用sql语句是最好。
[/Quote]
xiangyun_1224 2011-09-16
  • 打赏
  • 举报
回复
17楼的会将所有数据导出。用sql语句是最好。
geniuswjt 2011-09-16
  • 打赏
  • 举报
回复
17楼试过么[Quote=引用 19 楼 xiangyun_1224 的回复:]
第一题的答案以10楼--晴天为接近答案,不完全正确的原因是这样导不出的列名主键,但是大概意思是我想要的。
弱弱的说明一哈:如果是大篇幅的答案还不如直接创建新表,这样比导出要直接些。(不过大篇幅中也有丰富的含金量,只是思维复杂了点。)
[/Quote]
xiangyun_1224 2011-09-16
  • 打赏
  • 举报
回复
第一题的答案以10楼--晴天为接近答案,不完全正确的原因是这样导不出的列名主键,但是大概意思是我想要的。
弱弱的说明一哈:如果是大篇幅的答案还不如直接创建新表,这样比导出要直接些。(不过大篇幅中也有丰富的含金量,只是思维复杂了点。)
xiangyun_1224 2011-09-16
  • 打赏
  • 举报
回复
第二题的答案以12楼--小F为理想答案,后面又有14楼--爱新觉罗.毓华详细描述,方便了以后来者理解。在此为以后需要的人 谢谢二位。

一题正在验证中……
geniuswjt 2011-09-16
  • 打赏
  • 举报
回复
习惯了,汗
应该是对着数据库右键->任务->生成脚本
然后它会让你选你要导出的表、存储过程、函数等
你只选所有表就好,一步步往后,最后确定,就等着它一次性把表结构全生成了就好[Quote=引用 5 楼 xoxo_ 的回复:]
引用 2 楼 geniuswjt 的回复:
SQL code

1.用sql自带的导入导出,方法:对着数据库右键->任务->导出数据
2.truncate table tb


1.用sql自带的导入导出,方法:对着数据库右键->任务->导出数据
这个不是会把数据内容一起导出去的吗。
[/Quote]
怪众生太美丽 2011-09-16
  • 打赏
  • 举报
回复

--查询当前数据库各表表结构:
SELECT
表名=case when a.colorder=1 then d.name else '' end,
字段序号=a.colorder,
字段名=a.name,
标识=case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end,
主键=case when exists(SELECT 1 FROM sysobjects where xtype='PK' and name in (
SELECT name FROM sysindexes WHERE indid in(
SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid
))) then '√' else '' end,
类型=b.name,
占用字节数=a.length,
长度=COLUMNPROPERTY(a.id,a.name,'PRECISION'),
小数位数=isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0),
允许空=case when a.isnullable=1 then '√'else '' end,
默认值=isnull(e.text,'')
FROM syscolumns a
left join systypes b on a.xtype=b.xusertype
inner join sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties' and d.name<>'sysdiagrams'
left join syscomments e on a.cdefault=e.id
order by a.id,a.colorder

------单表查询
SELECT TOP 300 B.NAME,C.NAME AS DATATYPE,B.MAX_LENGTH,B.PRECISION,
B.SCALE,CASE WHEN is_nullable=1 THEN '√' else ' ' END AS IS_NULL
FROM STU_PLAN.SYS.ALL_OBJECTS A,STU_PLAN.SYS.ALL_COLUMNS B,STU_PLAN.SYS.SYSTYPES C
WHERE A.TYPE='U' AND A.OBJECT_ID=B.OBJECT_ID AND B.SYSTEM_TYPE_ID=C.XTYPE
AND A.NAME='U_USER'
ORDER BY B.COLUMN_ID
clee1021 2011-09-16
  • 打赏
  • 举报
回复
1.
SELECT d.name, B.name,C.name ,B.length,B.isnullable,B.colid FROM sys.sysobjects A,sys.syscolumns B,sys.systypes C,sys.tables D
WHERE A.xtype ='U' AND A.id =B.id AND B.xtype =C.xtype AND D.object_id = A.id ORDER BY D.name, B.colid

2.
truncate table XX
dawugui 2011-09-16
  • 打赏
  • 举报
回复
[Quote=引用楼主 xiangyun_1224 的回复:]
1.怎么只导出数据库中表的字段及数据类型,不导出数据内容?
2.字段ID为自动增长,删除数据后,怎么设置ID重新从1开始自动增长?
[/Quote]
1.--查询出各(某)表字段的属性

--sql server 2000
SELECT
表名 = case when a.colorder=1 then d.name else '' end,
表说明 = case when a.colorder=1 then isnull(f.value,'') else '' end,
字段序号 = a.colorder,
字段名 = a.name,
标识 = case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end,
主键 = case when exists(SELECT 1 FROM sysobjects where xtype='PK' and parent_obj=a.id and name in (
SELECT name FROM sysindexes WHERE indid in(
SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid))) then '√' else '' end,
类型 = b.name,
占用字节数 = a.length,
长度 = COLUMNPROPERTY(a.id,a.name,'PRECISION'),
小数位数 = isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0),
允许空 = case when a.isnullable=1 then '√'else '' end,
默认值 = isnull(e.text,''),
字段说明 = isnull(g.[value],'')
FROM
syscolumns a
left join
systypes b
on
a.xusertype=b.xusertype
inner join
sysobjects d
on
a.id=d.id and d.xtype='U' and d.name<>'dtproperties'
left join
syscomments e
on
a.cdefault=e.id
left join
sysproperties g
on
a.id=g.id and a.colid=g.smallid
left join
sysproperties f
on
d.id=f.id and f.smallid=0
where
d.name='要查询的表' --如果只查询指定表,加上此条件
order by
a.id,a.colorder

/*
表名 表说明 字段序号 字段名 标识 主键 类型 占用字节数 长度 小数位数 允许空 默认值 字段说明
------- ----- ------- -------- ---- ------- ------ ------- --------------- ------ ---------- ----------
authors 1 au_id √ id 11 11 0
2 au_lname varchar 40 40 0
3 au_fname varchar 20 20 0
4 phone char 12 12 0 ('UNKNOWN')
5 address varchar 40 40 0 √
6 city varchar 20 20 0 √
7 state char 2 2 0 √
8 zip char 5 5 0 √
9 contract bit 1 1 0
(所影响的行数为 9 行)
*/




--sql server 2005
-- 1. 表结构信息查询
-- ========================================================================
-- 表结构信息查询
-- 邹建 2005.08(引用请保留此信息)
-- ========================================================================
SELECT
TableName=CASE WHEN C.column_id=1 THEN O.name ELSE N'' END,
TableDesc=ISNULL(CASE WHEN C.column_id=1 THEN PTB.[value] END,N''),
Column_id=C.column_id,
ColumnName=C.name,
PrimaryKey=ISNULL(IDX.PrimaryKey,N''),
[IDENTITY]=CASE WHEN C.is_identity=1 THEN N'√'ELSE N'' END,
Computed=CASE WHEN C.is_computed=1 THEN N'√'ELSE N'' END,
Type=T.name,
Length=C.max_length,
Precision=C.precision,
Scale=C.scale,
NullAble=CASE WHEN C.is_nullable=1 THEN N'√'ELSE N'' END,
[Default]=ISNULL(D.definition,N''),
ColumnDesc=ISNULL(PFD.[value],N''),
IndexName=ISNULL(IDX.IndexName,N''),
IndexSort=ISNULL(IDX.Sort,N''),
Create_Date=O.Create_Date,
Modify_Date=O.Modify_date
FROM sys.columns C
INNER JOIN sys.objects O
ON C.[object_id]=O.[object_id]
AND O.type='U'
AND O.is_ms_shipped=0
INNER JOIN sys.types T
ON C.user_type_id=T.user_type_id
LEFT JOIN sys.default_constraints D
ON C.[object_id]=D.parent_object_id
AND C.column_id=D.parent_column_id
AND C.default_object_id=D.[object_id]
LEFT JOIN sys.extended_properties PFD
ON PFD.class=1
AND C.[object_id]=PFD.major_id
AND C.column_id=PFD.minor_id
-- AND PFD.name='Caption' -- 字段说明对应的描述名称(一个字段可以添加多个不同name的描述)
LEFT JOIN sys.extended_properties PTB
ON PTB.class=1
AND PTB.minor_id=0
AND C.[object_id]=PTB.major_id
-- AND PFD.name='Caption' -- 表说明对应的描述名称(一个表可以添加多个不同name的描述)
LEFT JOIN -- 索引及主键信息
(
SELECT
IDXC.[object_id],
IDXC.column_id,
Sort=CASE INDEXKEY_PROPERTY(IDXC.[object_id],IDXC.index_id,IDXC.index_column_id,'IsDescending')
WHEN 1 THEN 'DESC' WHEN 0 THEN 'ASC' ELSE '' END,
PrimaryKey=CASE WHEN IDX.is_primary_key=1 THEN N'√'ELSE N'' END,
IndexName=IDX.Name
FROM sys.indexes IDX
INNER JOIN sys.index_columns IDXC
ON IDX.[object_id]=IDXC.[object_id]
AND IDX.index_id=IDXC.index_id
LEFT JOIN sys.key_constraints KC
ON IDX.[object_id]=KC.[parent_object_id]
AND IDX.index_id=KC.unique_index_id
INNER JOIN -- 对于一个列包含多个索引的情况,只显示第1个索引信息
(
SELECT [object_id], Column_id, index_id=MIN(index_id)
FROM sys.index_columns
GROUP BY [object_id], Column_id
) IDXCUQ
ON IDXC.[object_id]=IDXCUQ.[object_id]
AND IDXC.Column_id=IDXCUQ.Column_id
AND IDXC.index_id=IDXCUQ.index_id
) IDX
ON C.[object_id]=IDX.[object_id]
AND C.column_id=IDX.column_id
-- WHERE O.name=N'要查询的表' -- 如果只查询指定表,加上此条件
ORDER BY O.name,C.column_id

-- 2. 索引及主键信息
-- ========================================================================
-- 索引及主键信息
-- 邹建 2005.08(引用请保留此信息)
-- ========================================================================
SELECT
TableId=O.[object_id],
TableName=O.Name,
IndexId=ISNULL(KC.[object_id],IDX.index_id),
IndexName=IDX.Name,
IndexType=ISNULL(KC.type_desc,'Index'),
Index_Column_id=IDXC.index_column_id,
ColumnID=C.Column_id,
ColumnName=C.Name,
Sort=CASE INDEXKEY_PROPERTY(IDXC.[object_id],IDXC.index_id,IDXC.index_column_id,'IsDescending')
WHEN 1 THEN 'DESC' WHEN 0 THEN 'ASC' ELSE '' END,
PrimaryKey=CASE WHEN IDX.is_primary_key=1 THEN N'√'ELSE N'' END,
[UQIQUE]=CASE WHEN IDX.is_unique=1 THEN N'√'ELSE N'' END,
Ignore_dup_key=CASE WHEN IDX.ignore_dup_key=1 THEN N'√'ELSE N'' END,
Disabled=CASE WHEN IDX.is_disabled=1 THEN N'√'ELSE N'' END,
Fill_factor=IDX.fill_factor,
Padded=CASE WHEN IDX.is_padded=1 THEN N'√'ELSE N'' END
FROM sys.indexes IDX
INNER JOIN sys.index_columns IDXC
ON IDX.[object_id]=IDXC.[object_id]
AND IDX.index_id=IDXC.index_id
LEFT JOIN sys.key_constraints KC
ON IDX.[object_id]=KC.[parent_object_id]
AND IDX.index_id=KC.unique_index_id
INNER JOIN sys.objects O
ON O.[object_id]=IDX.[object_id]
INNER JOIN sys.columns C
ON O.[object_id]=C.[object_id]
AND O.type='U'
AND O.is_ms_shipped=0
AND IDXC.Column_id=C.Column_id
-- INNER JOIN -- 对于一个列包含多个索引的情况,只显示第1个索引信息
-- (
-- SELECT [object_id], Column_id, index_id=MIN(index_id)
-- FROM sys.index_columns
-- GROUP BY [object_id], Column_id
-- ) IDXCUQ
-- ON IDXC.[object_id]=IDXCUQ.[object_id]
-- AND IDXC.Column_id=IDXCUQ.Column_id


2.

SQL Server 自增字段归零

SQL Server 2005表自增字段在多次插入数据后,自增的ID号比较混乱,而且删除数据后,自增的ID并不会归零,有两种方法可以控制自增的字段:

方法一: 如果曾经的数据都不需要的话,可以直接清空所有数据,并将自增字段恢复从1开始计数

truncate table 表名

方法二: dbcc checkident ('table_name', reseed, new_reseed_value) 当前值设置为 new_reseed_value。如果自创建表后没有将行插入该表,则在执行 DBCC CHECKIDENT 后插入的第一行将使用 new_reseed_value 作为标识。否则,下一个插入的行将使用 new_reseed_value + 1。如果 new_reseed_value 的值小于标识列中的最大值,以后引用该表时将产生 2627 号错误信息。

方法二不会清空已有数据,操作比较灵活,不仅可以将自增值归零,也适用于删除大量连续行后,重新设置自增值并插入新的数据;或从新的值开始,当然不能和已有的冲突。

当表中有了一下数据后
执行语句 DBCC CHECKIDENT ('dbo.Person', RESEED, 10) 后再通过Management Studio添加数据时,ID就会自动从11开始,也就是new_reseed_value+1开始
xiangyun_1224 2011-09-16
  • 打赏
  • 举报
回复
哦~ 谢谢各位 在这里我说一声对不起哈。
1.怎么只导出数据库中表的列名及数据类型,不导出数据内容? 我把“列名”打成“字段”了。实在不好意思。为了深表歉意我将分值增加一倍。
--小F-- 2011-09-15
  • 打赏
  • 举报
回复
字段ID为自动增长,删除数据后,怎么设置ID重新从1开始自动增长?

这个是全部清空么??


--使用dbcc checkident检查和设置表的标识值
create table tb
(
id int primary key identity,
name varchar(50)
)
insert into tb
select 'a'
union all
select 'b'
union all
select 'c'
union all
select 'd'
go
dbcc checkident(tb,noreseed)
go


delete from tb where id>2
go
--删除记录后,表tb只剩下两条记录了,但是此时表tb的标识值仍为4,可以用下面的语句重置标识值为2
dbcc checkident(tb,reseed,2)
go

dbcc checkident(tb,noreseed)
go

/*检查标识信息: 当前标识值 '4',当前列值 '4'。
DBCC 执行完毕。如果 DBCC 输出了错误信息,请与系统管理员联系。

(2 行受影响)
检查标识信息: 当前标识值 '4',当前列值 '2'。
DBCC 执行完毕。如果 DBCC 输出了错误信息,请与系统管理员联系。
检查标识信息: 当前标识值 '2',当前列值 '2'。
DBCC 执行完毕。如果 DBCC 输出了错误信息,请与系统管理员联系。*/

drop table tb
--小F-- 2011-09-15
  • 打赏
  • 举报
回复
第一题:
****************************************************************************
软件名称: May Flower Erp
版权所有: (C) 2005-2006 May Flower ERP 开发组
功能描述: 获取指定表的创建脚本,包括表和字段的属性、外键(注释掉的)
----------------------------------------------------------------------------
参数列表:
1: @TableName 需要创建脚本的表的名称
****************************************************************************
|</PRE>*/
CREATE PROCEDURE [dbo].[sp_HelpTable](@TableName sysname)
AS
SET NOCOUNT ON
DECLARE @ObjectID int
DECLARE @TableScript table(Iden int IDENTITY(1, 1), ScriptLine nvarchar(4000))
SET @ObjectID = object_id(@TableName)
IF @ObjectID IS NULL OR OBJECTPROPERTY(@ObjectID, 'IsTable') = 0
BEGIN
RAISERROR('指定的对象不是表对象', 16, 1)
RETURN
END
--获取表的创建脚本
--插入表头
INSERT INTO @TableScript(ScriptLine)
SELECT N'CREATE TABLE [' + USER_NAME(OBJECTPROPERTY(@ObjectID, N'OwnerId')) + N'].[' + object_name(@ObjectID) + N']('

--插入字段
INSERT INTO @TableScript(ScriptLine)
SELECT N' [' + a.Name + N'] [' + b.name + N']' +
CASE WHEN c.Object_id IS NOT NULL THEN N' IDENTITY(' + CONVERT(nvarchar, c.seed_value) + N', ' + CONVERT(nvarchar, c.increment_value) + N')'
ELSE '' END +
CASE WHEN b.xusertype IN (167, 175, 231, 239) THEN N'('+CONVERT(nvarchar, a.prec) + N')'
WHEN b.xusertype in (106, 108) THEN N'('+CONVERT(nvarchar, a.xprec) + N', ' + CONVERT(nvarchar, a.xscale) + N')'
ELSE '' END +
CASE a.isnullable WHEN 1 THEN N'' ELSE N' NOT' END + N' NULL' +
CASE WHEN d.Name IS NOT NULL THEN N' DEFAULT ' + d.Definition ELSE N'' END +
N','
FROM sys.syscolumns a
LEFT JOIN sys.systypes b ON a.xusertype = b.xusertype
LEFT JOIN sys.identity_columns c ON c.Object_id = a.ID AND c.Column_ID = a.ColID
LEFT JOIN sys.default_constraints d ON d.Parent_Object_ID = a.ID AND d.Parent_column_ID = a.ColID
WHERE a.[ID] = @ObjectID
ORDER BY a.ColOrder

--插入主键和索引
DECLARE @IndexID int, @IndexScript nvarchar(4000)
DECLARE IndexCursor CURSOR FOR
SELECT b.Index_ID, N' CONSTRAINT [' + a.Name + N'] ' +
CASE a.Type WHEN 'PK' THEN N'PRIMARY KEY ' WHEN 'UQ' THEN N'UNIQUE ' END +
CASE b.Type WHEN 1 THEN N'CLUSTERED' WHEN 2 THEN N'NONCLUSTERED ' END + N'('
FROM sys.key_constraints a
LEFT JOIN sys.indexes b ON b.Object_ID = a.Parent_Object_ID AND b.index_id = a.unique_index_id
WHERE a.Parent_Object_ID = @ObjectID
OPEN IndexCursor
FETCH NEXT FROM IndexCursor INTO @IndexID, @IndexScript
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @IndexScript = @IndexScript + N'[' + INDEX_COL(object_name(@ObjectID), 2 , 1) + N'],'
FROM sys.index_columns
WHERE Object_ID = @ObjectID
AND Index_ID = 2

SET @IndexScript = LEFT(@IndexScript, LEN(@IndexScript) -1) + N'),'
INSERT INTO @TableScript(ScriptLine) VALUES(@IndexScript)
FETCH NEXT FROM IndexCursor INTO @IndexID, @IndexScript
END
Close IndexCursor
DEALLOCATE IndexCursor

insert into @TableScript(ScriptLine) VALUES(')')
select * from @TableScript
-晴天 2011-09-15
  • 打赏
  • 举报
回复
--1. 原表为 tb,新生成一个表为 newtable
select * into newtable from tb where 1=0

--2.删除所有数据时不用delete,而是执行:
truncate table tb
兔子侠客 2011-09-15
  • 打赏
  • 举报
回复
MARK!!
jiemo587 2011-09-15
  • 打赏
  • 举报
回复

--1.怎么只导出数据库中表的字段及数据类型,不导出数据内容?

--将表的字段及数据类型,然后再导出表B,不知道我理解错了没有。。。
select a.name as [字段名],b.name as [数据类型],a.length as [长度] into 表名B
from syscolumns a join systypes b on a.xtype=b.xtype
where b.status='0'
and a.id=OBJECT_ID(N'表名A')




--2.字段ID为自动增长,删除数据后,怎么设置ID重新从1开始自动增长?
truncate table tb



小笨熊 2011-09-15
  • 打赏
  • 举报
回复
ls已经回答了
lflljt 2011-09-15
  • 打赏
  • 举报
回复
1.生成脚本:企业管理器-选中表右键-所有任务-生成sql脚本
2.DBCC重置标识列
加载更多回复(5)

22,206

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧