|
--
?======================================================
--
列出SQL?SERVER?所有表，字段名，主键，类型，长度，小数位数等信息
--
在查询分析器里运行即可,可以生成一个表，导出到EXCEL中
--
?======================================================
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
?(
SELECT
?
count
(
*
)
???????
FROM
?sysobjects
???????
WHERE
?(name?
in
?????????????????(
SELECT
?name
????????????????
FROM
?sysindexes
????????????????
WHERE
?(id?
=
?a.id)?
AND
?(indid?
in
??????????????????????????(
SELECT
?indid
?????????????????????????
FROM
?sysindexkeys
?????????????????????????
WHERE
?(id?
=
?a.id)?
AND
?(colid?
in
???????????????????????????????????(
SELECT
?colid
??????????????????????????????????
FROM
?syscolumns
??????????????????????????????????
WHERE
?(id?
=
?a.id)?
AND
?(name?
=
?a.name)))))))?
AND
??????????????(xtype?
=
?
'
PK
'
))
>
0
?
then
?
'
√
'
?
else
?
''
?
end
)?主键,
???????b.name?类型,
???????a.length?占用字节数,
???????
COLUMNPROPERTY
(a.id,
'
PRECISION
'
)?
as
?长度,
???????
isnull
(
COLUMNPROPERTY
(a.id,
'
Scale
'
),
0
)?
as
?小数位数,
???????(
case
?
when
?a.isnullable
=
1
?
then
?
'
√
'
else
?
''
?
end
)?允许空,
???????
isnull
(e.
text
,
''
)?默认值,
???????
isnull
(g.
[
value
]
,
''
)?
AS
?字段说明????
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
'
left
?
join
?syscomments?e
on
?a.cdefault
=
e.id
left
?
join
?sysproperties?g
on
?a.id
=
g.id?
AND
?a.colid?
=
?g.smallid??
order
?
by
?a.id,a.colorder
--
-----------------------------------------------------------------------------------------------
列出SQL?SERVER?所有表、字段定义，类型，长度，一个值等信息
并导出到Excel?中
--
?======================================================
--
?Export?all?user?tables?definition?and?one?sample?value
--
?jan-13-2003,Dr.Zhang
--
?======================================================
在查询分析器里运行：
SET
?ANSI_NULLS?
OFF
?
GO
SET
?NOCOUNT?
ON
GO
SET
?LANGUAGE?
'
Simplified?Chinese
'
go
DECLARE
?
@tbl
?
nvarchar
(
200
),
@fld
?
nvarchar
(
200
),
@sql
?
nvarchar
(
4000
),
@maxlen
?
int
,
@sample
?
nvarchar
(
40
)
SELECT
?d.name?TableName,a.name?FieldName,b.name?TypeName,a.length?Length,a.isnullable?IS_NULL?
INTO
?#t
FROM
??syscolumns??a,??systypes?b,sysobjects?d??
WHERE
??a.xtype
=
b.xusertype??
and
??a.id
=
d.id??
and
??d.xtype
=
'
U
'
?
DECLARE
?read_cursor?
CURSOR
FOR
?
SELECT
?TableName,FieldName?
FROM
?#t
SELECT
?
TOP
?
1
?
'
_TableName?????????????????????
'
?TableName,
????????????
'
FieldName??????????????????????
'
?FieldName,
'
TypeName?????????????
'
?TypeName,
????????????
'
Length
'
?Length,
'
IS_NULL
'
?IS_NULL,?
????????????
'
MaxLenUsed
'
?
AS
?MaxLenUsed,
'
Sample?Value??????????
'
?Sample,
?????????????
'
Comment???
'
?Comment?
INTO
?#tc?
FROM
?#t
OPEN
?read_cursor
FETCH
?
NEXT
?
FROM
?read_cursor?
INTO
?
@tbl
,
@fld
WHILE
?(
@@fetch_status
?
<>
?
-
1
)??
--
-?failes
BEGIN
???????
IF
?(
@@fetch_status
?
<>
?
-
2
)?
--
?Missing
???????
BEGIN
??????????????
SET
?
@sql
=
N
'
SET?@maxlen=(SELECT?max(len(cast(
'
+
@fld
+
'
?as?nvarchar)))?FROM?
'
+
@tbl
+
'
)
'
??????????????
--
PRINT?@sql
??????????????
EXEC
?SP_EXECUTESQL?
@sql
,N
'
@maxlen?int?OUTPUT
'
,
@maxlen
?OUTPUT
??????????????
--
print?@maxlen
??????????????
SET
?
@sql
=
N
'
SET?@sample=(SELECT?TOP?1?cast(
'
+
@fld
+
'
?as?nvarchar)?FROM?
'
+
@tbl
+
'
?WHERE?len(cast(
'
+
@fld
+
'
?as?nvarchar))=
'
+
convert
(
nvarchar
(
5
),
@maxlen
)
+
'
)
'
??????????????
EXEC
?SP_EXECUTESQL?
@sql
,N
'
@sample?varchar(30)?OUTPUT
'
,
@sample
?OUTPUT
??????????????
--
for?quickly???
??????????????
--
SET?@sql=N'SET?@sample=convert(varchar(20),(SELECT?TOP?1?'+@fld+'?FROM?'+
?????????????????????
--
@tbl+'?order?by?1?desc?))'??
??????????????
PRINT
?
@sql
??????????????
print
?
@sample
??????????????
print
?
@tbl
??????????????
EXEC
?SP_EXECUTESQL?
@sql
,N
'
@sample?nvarchar(30)?OUTPUT
'
,
@sample
?OUTPUT
??????????????
INSERT
?
INTO
?#tc?
SELECT
?
*
,
ltrim
(
ISNULL
(
@maxlen
,
0
))?
as
?MaxLenUsed,
?????????????????????
convert
(
nchar
(
20
),
ltrim
(
ISNULL
(
@sample
,
'
?
'
)))?
as
?Sample,
'
?
'
?Comment?
FROM
?#t?
where
?TableName
=
@tbl
?
and
?FieldName
=
@fld
???????
END
???????
FETCH
?
NEXT
?
FROM
?read_cursor?
INTO
?
@tbl
,
@fld
END
CLOSE
?read_cursor
DEALLOCATE
?read_cursor
GO
SET
?ANSI_NULLS?
ON
GO
SET
?NOCOUNT?
OFF
GO
select
?
count
(
*
)??
from
?#t
DROP
?
TABLE
?#t
GO
select
?
count
(
*
)
-
1
??
from
?#tc
select
?
*
?
into
?##tx?
from
?#tc?
order
?
by
?tablename
DROP
?
TABLE
?#tc
--
select?*?from?##tx
declare
?
@db
?
nvarchar
(
60
),
@sql
?
nvarchar
(
3000
)
set
?
@db
=
db_name
()
--
请修改用户名和口令?导出到Excel?中
set
?
@sql
=
'
exec?master.dbo.xp_cmdshell?
''
bcp?..dbo.##tx?out?c:
'
+
@db
+
'
_exp.xls?-w?-C936?-Usa?-Psa?
'''
print
?
@sql
exec
(
@sql
)
GO
DROP
?
TABLE
?##tx
GO
--
?======================================================
--
根据表中数据生成insert语句的存储过程
--
建立存储过程，执行?spGenInsertSQL?表名
--
感谢playyuer
--
?======================================================
CREATE
???
proc
?spGenInsertSQL?(
@tablename
?
varchar
(
256
))
as
begin
??
declare
?
@sql
?
varchar
(
8000
)
??
declare
?
@sqlValues
?
varchar
(
8000
)
??
set
?
@sql
?
=
'
?(
'
??
set
?
@sqlValues
?
=
?
'
values?(
''
+
'
??
select
?
@sqlValues
?
=
?
@sqlValues
?
+
?cols?
+
?
'
?+?
''
,
''
?+?
'
?,
@sql
?
=
?
@sql
?
+
?
'
[
'
?
+
?name?
+
?
'
],
'
?
????
from
?
????????(
select
?
case
?
??????????????????
when
?xtype?
in
?(
48
,
52
,
56
,
59
,
60
,
62
,
104
,
106
,
108
,
122
,
127
)????????????????????????????????
???????????????????????
then
?
'
case?when?
'
+
?name?
+
'
?is?null?then?
''
NULL
''
?else?
'
?
+
?
'
cast(
'
+
?name?
+
?
'
?as?varchar)
'
+
'
?end
'
??????????????????
when
?xtype?
in
?(
58
,
61
)
???????????????????????
then
?
'
case?when?
'
+
?name?
+
'
?is?null?then?
''
NULL
''
?else?
'
+
'''''''''
?+?
'
?
+
?
'
cast(
'
+
?name?
+
'
?as?varchar)
'
+
?
'
+
'''''''''
+
'
?end
'
?????????????????
when
?xtype?
in
?(
167
)
???????????????????????
then
?
'
case?when?
'
+
?name?
+
'
?is?null?then?
''
NULL
''
?else?
'
+
'''''''''
?+?
'
?
+
?
'
replace(
'
+
?name
+
'
,
''''''''
,
''''''''''''
)
'
?
+
?
'
+
'''''''''
+
'
?end
'
??????????????????
when
?xtype?
in
?(
231
)
???????????????????????
then
?
'
case?when?
'
+
?name?
+
'
?is?null?then?
''
NULL
''
?else?
'
+
'''
N
''''''
?+?
'
?
+
?
'
replace(
'
+
?name
+
'
,
''''''''''''
)
'
?
+
?
'
+
'''''''''
+
'
?end
'
??????????????????
when
?xtype?
in
?(
175
)
???????????????????????
then
?
'
case?when?
'
+
?name?
+
'
?is?null?then?
''
NULL
''
?else?
'
+
'''''''''
?+?
'
?
+
?
'
cast(replace(
'
+
?name
+
'
,
''''''''''''
)?as?Char(
'
?
+
?
cast
(length?
as
?
varchar
)??
+
?
'
))+
'''''''''
+
'
?end
'
??????????????????
when
?xtype?
in
?(
239
)
???????????????????????
then
?
'
case?when?
'
+
?name?
+
'
?is?null?then?
''
NULL
''
?else?
'
+
'''
N
''''''
?+?
'
?
+
?
'
cast(replace(
'
+
?name
+
'
,
''''''''''''
)?as?Char(
'
?
+
?
cast
(length?
as
?
varchar
)??
+
?
'
))+
'''''''''
+
'
?end
'
??????????????????
else
?
'''
NULL
'''
????????????????
end
?
as
?Cols,name
???????????
from
?syscolumns??
??????????
where
?id?
=
?
object_id
(
@tablename
)?
????????)?T?
??
set
?
@sql
?
=
'
select?
''
INSERT?INTO?[
'
+
?
@tablename
?
+
?
'
]
'
?
+
?
left
(
@sql
,
len
(
@sql
)
-
1
)
+
'
)?
'
?
+
?
left
(
@sqlValues
,
len
(
@sqlValues
)
-
4
)?
+
?
'
)
''
?from?
'
+
@tablename
??
--
print?@sql
??
exec
?(
@sql
)
end
GO
--
?======================================================
--
根据表中数据生成insert语句的存储过程
--
建立存储过程，执行?proc_insert?表名
--
感谢Sky_blue
--
?======================================================
CREATE
?
proc
?proc_insert?(
@tablename
?
varchar
(
256
))
as
begin
???????
set
?nocount?
on
???????
declare
?
@sqlstr
?
varchar
(
4000
)
???????
declare
?
@sqlstr1
?
varchar
(
4000
)
???????
declare
?
@sqlstr2
?
varchar
(
4000
)
???????
select
?
@sqlstr
=
'
select?
''
insert?
'
+
@tablename
???????
select
?
@sqlstr1
=
''
???????
select
?
@sqlstr2
=
'
?(
'
???????
select
?
@sqlstr1
=
?
'
?values?(?
''
+
'
???????
select
?
@sqlstr1
=
@sqlstr1
+
col
+
'
+
''
,
''
+
'
?,
@sqlstr2
=
@sqlstr2
+
name?
+
'
,
'
?
from
?(
select
?
case
?
--
?????when?a.xtype?=173?then?'case?when?'+a.name+'?is?null?then?''NULL''?else?'+'convert(varchar('+convert(varchar(4),a.length*2+2)+'),'+a.name?+')'+'?end'
???????
when
?a.xtype?
=
104
?
then
?
'
case?when?
'
+
a.name
+
'
?is?null?then?
''
NULL
''
?else?
'
+
'
convert(varchar(1),
'
+
a.name?
+
'
)
'
+
'
?end
'
???????
when
?a.xtype?
=
175
?
then
?
'
case?when?
'
+
a.name
+
'
?is?null?then?
''
NULL
''
?else?
'
+
'''''''''
+
'
+
'
replace(
'
+
a.name
+
'
,
''''''''''''
)
'
?
+
?
'
+
'''''''''
+
'
?end
'
???????
when
?a.xtype?
=
61
??
then
?
'
case?when?
'
+
a.name
+
'
?is?null?then?
''
NULL
''
?else?
'
+
'''''''''
+
'
+
'
convert(varchar(23),
'
+
a.name?
+
'
,121)
'
+
?
'
+
'''''''''
+
'
?end
'
???????
when
?a.xtype?
=
106
?
then
?
'
case?when?
'
+
a.name
+
'
?is?null?then?
''
NULL
''
?else?
'
+
'
convert(varchar(
'
+
convert
(
varchar
(
4
),a.xprec
+
2
)
+
'
),
'
+
a.name?
+
'
)
'
+
'
?end
'
???????
when
?a.xtype?
=
62
??
then
?
'
case?when?
'
+
a.name
+
'
?is?null?then?
''
NULL
''
?else?
'
+
'
convert(varchar(23),2)
'
+
'
?end
'
???????
when
?a.xtype?
=
56
??
then
?
'
case?when?
'
+
a.name
+
'
?is?null?then?
''
NULL
''
?else?
'
+
'
convert(varchar(11),
'
+
a.name?
+
'
)
'
+
'
?end
'
???????
when
?a.xtype?
=
60
??
then
?
'
case?when?
'
+
a.name
+
'
?is?null?then?
''
NULL
''
?else?
'
+
'
convert(varchar(22),
'
+
a.name?
+
'
)
'
+
'
?end
'
???????
when
?a.xtype?
=
239
?
then
?
'
case?when?
'
+
a.name
+
'
?is?null?then?
''
NULL
''
?else?
'
+
'''''''''
+
'
+
'
replace(
'
+
a.name
+
'
,
''''''''''''
)
'
?
+
?
'
+
'''''''''
+
'
?end
'
???????
when
?a.xtype?
=
108
?
then
?
'
case?when?
'
+
a.name
+
'
?is?null?then?
''
NULL
''
?else?
'
+
'
convert(varchar(
'
+
convert
(
varchar
(
4
),
'
+
a.name?
+
'
)
'
+
'
?end
'
???????
when
?a.xtype?
=
231
?
then
?
'
case?when?
'
+
a.name
+
'
?is?null?then?
''
NULL
''
?else?
'
+
'''''''''
+
'
+
'
replace(
'
+
a.name
+
'
,
''''''''''''
)
'
?
+
?
'
+
'''''''''
+
'
?end
'
???????
when
?a.xtype?
=
59
??
then
?
'
case?when?
'
+
a.name
+
'
?is?null?then?
''
NULL
''
?else?
'
+
'
convert(varchar(23),2)
'
+
'
?end
'
???????
when
?a.xtype?
=
58
??
then
?
'
case?when?
'
+
a.name
+
'
?is?null?then?
''
NULL
''
?else?
'
+
'''''''''
+
'
+
'
convert(varchar(23),121)
'
+
?
'
+
'''''''''
+
'
?end
'
???????
when
?a.xtype?
=
52
??
then
?
'
case?when?
'
+
a.name
+
'
?is?null?then?
''
NULL
''
?else?
'
+
'
convert(varchar(12),
'
+
a.name?
+
'
)
'
+
'
?end
'
???????
when
?a.xtype?
=
122
?
then
?
'
case?when?
'
+
a.name
+
'
?is?null?then?
''
NULL
''
?else?
'
+
'
convert(varchar(22),
'
+
a.name?
+
'
)
'
+
'
?end
'
???????
when
?a.xtype?
=
48
??
then
?
'
case?when?
'
+
a.name
+
'
?is?null?then?
''
NULL
''
?else?
'
+
'
convert(varchar(6),
'
+
a.name?
+
'
)
'
+
'
?end
'
--
?????when?a.xtype?=165?then?'case?when?'+a.name+'?is?null?then?''NULL''?else?'+'convert(varchar('+convert(varchar(4),'+a.name?+')'+'?end'
???????
when
?a.xtype?
=
167
?
then
?
'
case?when?
'
+
a.name
+
'
?is?null?then?
''
NULL
''
?else?
'
+
'''''''''
+
'
+
'
replace(
'
+
a.name
+
'
,
''''''''''''
)
'
?
+
?
'
+
'''''''''
+
'
?end
'
???????
else
?
'''
NULL
'''
???????
end
?
as
?col,a.colid,a.name
???????
from
?syscolumns?a?
where
?a.id?
=
?
object_id
(
@tablename
)?
and
?a.xtype?
<>
189
?
and
?a.xtype?
<>
34
?
and
?a.xtype?
<>
35
?
and
??a.xtype?
<>
36
???????)t?
order
?
by
?colid
???????
???????
select
?
@sqlstr
=
@sqlstr
+
left
(
@sqlstr2
,
len
(
@sqlstr2
)
-
1
)
+
'
)?
'
+
left
(
@sqlstr1
,
len
(
@sqlstr1
)
-
3
)
+
'
)
''
?from?
'
+
@tablename
--
??print?@sqlstr
???????
exec
(?
@sqlstr
)
???????
set
?nocount?
off
end
GO
?
（编辑：李大同）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
