SqlServer 获取 当前地址下 所有数据库字段信息 / 快速 批量插入数据库(TVPs)
程序员文章站
2024-03-19 19:30:58
...
全网独一份 !!!
纯手工呕心沥血之作!!!
SQL执行
--拼装 当前地址下 所有数据库字段信息
BEGIN
DECLARE @dataBaseName NVARCHAR(MAX)--数据库名称
DECLARE @tableName NVARCHAR(MAX )--表名
DECLARE @sql NVARCHAR(MAX)--将要执行的sql
--判断是否存在,存在则删除临时表
IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE id = object_id(N'tempdb..##myParentTable') and type='U')
DROP TABLE ##myParentTable
IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE id = object_id(N'tempdb..##myChildTable') and type='U')
DROP TABLE ##myChildTable
--创建myParentTable临时表(存储 该地址下 所有表名)
--(存数据库名称和表名,数据不多,表有多少,数据有多少)
CREATE TABLE ##myParentTable
(
序号 INT IDENTITY(1,1),
数据库名称 NVARCHAR(MAX),
表名 NVARCHAR(MAX)
)
--创建myChildTable临时表
--(存储字段相关的信息,字段有多少个,数据就有多少,数据庞大,需要耐心等待) 据测试 1分钟左右 1万多条数据
CREATE TABLE ##myChildTable
(
序号 INT IDENTITY(1,1),
数据库名称 NVARCHAR(MAX),
表名 NVARCHAR(MAX),
列名 NVARCHAR(MAX),
列说明 NVARCHAR(MAX),
数据类型 NVARCHAR(MAX),
长度 INT,
小数位数 INT,
标识 NVARCHAR(MAX ),
主键 NVARCHAR(MAX ),
允许空 NVARCHAR(MAX ),
默认值 NVARCHAR(MAX )
)
--声明 Parent_Cursor 游标 (格式:DECLARE 游标名称 For 数据) (数据库名称)
DECLARE Parent_Cursor CURSOR FOR
SELECT name FROM sysdatabases WHERE name NOT IN('master','tempdb','model','msdb') --查询所有的数据库名称(排除系统数据库)
--打开游标--
OPEN Parent_Cursor
--开始循环游标变量(格式:)
FETCH NEXT FROM Parent_Cursor INTO @dataBaseName
WHILE @@FETCH_STATUS = 0 --返回被 FETCH语句执行的最后游标的状态--
BEGIN
SET @sql=' USE 'aaa@qq.com+';';
SET @sqlaaa@qq.comsql+'insert ##myParentTable (数据库名称,表名) (select'''aaa@qq.com+''', name from sysobjects where xtype=''U''); ';
EXEC(@sql)
FETCH NEXT FROM Parent_Cursor INTO @dataBaseName --转到下一个游标，没有会死循环
END
CLOSE Parent_Cursor --关闭游标
DEALLOCATE Parent_Cursor --释放游标
----------------------------------------------------------------------------
--声明游标 Child_Cursor (数据库名称, 表名)
DECLARE Child_Cursor CURSOR FOR
SELECT 数据库名称, 表名 from ##myParentTable
--打开游标--
OPEN Child_Cursor
--开始循环游标变量--
FETCH NEXT FROM Child_Cursor INTO @dataBaseName,@tableName
WHILE @@FETCH_STATUS = 0 --返回被 FETCH语句执行的最后游标的状态--
BEGIN
SET @sql=' USE 'aaa@qq.com+';';
SET @sqlaaa@qq.comsql+'INSERT INTO ##myChildTable (数据库名称,表名,列名,列说明,数据类型,长度,小数位数,标识,主键,允许空,默认值)(
SELECT
'''aaa@qq.com+''',
'''aaa@qq.com+''',
col.name AS 列名 ,
CONVERT(NVARCHAR(max),ISNULL(ep.[value], '''')) AS 列说明 ,
t.name AS 数据类型 ,
col.length AS 长度 ,
ISNULL(COLUMNPROPERTY(col.id, col.name, ''Scale''), 0) AS 小数位数 ,
CASE WHEN COLUMNPROPERTY(col.id, col.name, ''IsIdentity'') = 1 THEN ''√''
ELSE ''''
END AS 标识 ,
CASE WHEN EXISTS ( SELECT 1
FROM dbo.sysindexes si
INNER JOIN dbo.sysindexkeys sik ON si.id = sik.id
AND si.indid = sik.indid
INNER JOIN dbo.syscolumns sc ON sc.id = sik.id
AND sc.colid = sik.colid
INNER JOIN dbo.sysobjects so ON so.name = si.name
AND so.xtype = ''PK''
WHERE sc.id = col.id
AND sc.colid = col.colid ) THEN ''√''
ELSE ''''
END AS 主键 ,
CASE WHEN col.isnullable = 1 THEN ''√''
ELSE ''''
END AS 允许空 ,
ISNULL(comm.text, '''') AS 默认值
FROM dbo.syscolumns col
LEFT JOIN dbo.systypes t ON col.xtype = t.xusertype
inner JOIN dbo.sysobjects obj ON col.id = obj.id
AND obj.xtype = ''U''
AND obj.status >= 0
LEFT JOIN dbo.syscomments comm ON col.cdefault = comm.id
LEFT JOIN sys.extended_properties ep ON col.id = ep.major_id
AND col.colid = ep.minor_id
AND ep.name = ''MS_Description''
LEFT JOIN sys.extended_properties epTwo ON obj.id = epTwo.major_id
AND epTwo.minor_id = 0
AND epTwo.name = ''MS_Description''
WHERE obj.name = '''aaa@qq.com +'''
)';
EXEC(@sql) --执行sql
FETCH NEXT FROM Child_Cursor INTO @dataBaseName,@tableName --转到下一个游标，没有会死循环
END
CLOSE Child_Cursor --关闭游标
DEALLOCATE Child_Cursor --释放游标
END
GO
--end
--查看获得的数量
SELECT COUNT(1) FROM ##myChildTable
--经测试 : 2分 24秒 得到 17700 条 字段信息
--查看最终数据
SELECT TOP 1000 * FROM ##myChildTable
--检测临时表是否存在
IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE id=object_id(N'tempdb..##myChildTable'))
PRINT '存在'
ELSE
PRINT '不存在'
Main方法代码
namespace ConsoleApp3
{
class Program
{
static void Main(string[] args)
{
//数据库链接Model
SqlQueryModel model =new SqlQueryModel();
#region 查数据
//源数据地址
model = new SqlQueryModel()
{
Ip = "输入对应ip地址",
DataBase = "tempdb",//本次查数据随意指定一个数据库就可以.无影响
UserName = "账号",
UserPwd = "密码"
};
//配置连接字符串
SqlHelper sqlHelper = new SqlHelper(model);
//查全局临时表.(这个表咱们需要的所有数据)
var myTable = sqlHelper.GetTable("SELECT * FROM ##myChildTable", System.Data.CommandType.Text, null);
#endregion
#region 存数据
//目标数据地址
model = new SqlQueryModel()
{
Ip = ".",
DataBase = "MyDBInfo",//这次的数据库名称很重要.对应着数据存放地址
UserName = "sa",
UserPwd = "sa123"
};
//配置数据库链接
sqlHelper = new SqlHelper(model);
//执行数据TVPs批量插入
sqlHelper.TbaleValuedToDb(myTable);
#endregion
}
}
}
SqlHelper
using System;
using System.Data;
using System.Data.SqlClient;
namespace ConsoleApp3
{
public class SqlQueryModel
{
public string Ip { get; set; }
public string DataBase { get; set; }
public string UserName { get; set; }
public string UserPwd { get; set; }
public string SqlStr { get; set; }
}
public class SqlHelper
{
/// <summary>
/// sqlhelper
/// xuejie
/// 2018-07-17
/// </summary>
private string connStr = string.Empty;
public SqlHelper(SqlQueryModel model)
{
connStr = $"Data Source={model.Ip}; Initial Catalog={model.DataBase}; User ID={model.UserName}; Password={model.UserPwd};";
}
/// <summary>
/// 返回table
/// </summary>
/// <param name="sql">SQL语句</param>
/// <param name="type">参数化类型</param>
/// <param name="pars">SqlParameter数组</param>
/// <returns></returns>
public DataTable GetTable(string sql, CommandType type, params SqlParameter[] pars)
{
try
{
using (SqlConnection conn = new SqlConnection(connStr))
{
using (SqlDataAdapter atper = new SqlDataAdapter(sql, conn))
{
atper.SelectCommand.CommandType = type;
if (pars != null)
{
atper.SelectCommand.Parameters.AddRange(pars);
}
DataTable da = new DataTable();
atper.Fill(da);
return da;
}
}
}
catch (Exception e)
{
throw e;
}
}
#region [ 使用TVPs插入数据 ]
/// <summary>
/// 使用TVPs插入数据( 经测试 1万7千条数据需要 10s )
/// </summary>
/// <param name="dt">数据</param>
public void TbaleValuedToDb(DataTable dt)
{
/*
DROP TYPE BulkUdt --删除自定义表类型
--注意 首先需要手动在对应的数据库中 创建一个自定义表类型. 在SqlServer里 执行 下面这段代码
-- 创建一个 名称为 BulkUdt 的 自定义表类型
CREATE TYPE BulkUdt AS TABLE
(
序号 int ,
数据库名称 NVARCHAR(MAX),
表名 nvarchar(max),
列名 NVARCHAR(max),
列说明 NVARCHAR(MAX ),
数据类型 NVARCHAR(MAX ),
长度 INT,
小数位数 INT,
标识 NVARCHAR(MAX ),
主键 NVARCHAR(MAX ),
允许空 NVARCHAR(MAX ),
默认值 NVARCHAR(MAX )
)
*/
SqlConnection sqlconn = new SqlConnection(connStr);
string sqlStatement =
//指定数据库名称
"insert into 数据库名字 " +
//指定数据库字段 (如果全部需要插入可以写成 * 号)
"(序号,数据库名称,表名,列名,列说明,数据类型,长度,小数位数,标识,主键,允许空,默认值)" +
//指定 自定义表类型的字段名称 (如果需要全部插入 可以写成 * 号)
" SELECT nc.序号,nc.数据库名称,nc.表名,nc.列名,nc.列说明,nc.数据类型,nc.长度,nc.小数位数,nc.标识,nc.主键,nc.允许空,nc.默认值 " +
" FROM @TableData AS nc";
SqlCommand cmd = new SqlCommand(sqlStatement, sqlconn);
SqlParameter catParam = cmd.Parameters.AddWithValue("@TableData", dt);//传入dataTable数据
catParam.SqlDbType = SqlDbType.Structured;
//指定类型为 自定义表类型的名称
catParam.TypeName = "dbo.BulkUdt";
try
{
sqlconn.Open();
if (dt != null && dt.Rows.Count != 0)
{
//执行sql 返回受影响行数
cmd.ExecuteNonQuery();
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
sqlconn.Close();
}
/*
--Profiler 监测到 最后数据库执行的 sql
declare @p3 dbo.BulkUdt
insert into @p3 values(1,N'master',N'trace_xe_action_map',N'trace_column_id',N'',N'smallint',2,0,N'',N'',N'',N'')
insert into @p3 values(2,N'master',N'trace_xe_action_map',N'package_name',N'',N'nvarchar',120,0,N'',N'',N'',N'')
insert into @p3 values(3,N'master',N'trace_xe_action_map',N'xe_action_name',N'',N'nvarchar',120,0,N'',N'',N'',N'')
insert into @p3 values(4,N'master',N'trace_xe_event_map',N'trace_event_id',N'',N'smallint',2,0,N'',N'',N'',N'')
insert into @p3 values(5,N'master',N'trace_xe_event_map',N'package_name',N'',N'nvarchar',120,0,N'',N'',N'',N'')
...
...
...
exec sp_executesql N'insert into 数据库名字
(序号,数据库名称,表名,列名,列说明,数据类型,长度,小数位数,标识,主键,允许空,默认值)
SELECT nc.序号,nc.数据库名称,nc.表名,nc.列名,nc.列说明,nc.数据类型,nc.长度,nc.小数位数,nc.标识,nc.主键,nc.允许空,nc.默认值
FROM @TableData AS nc',N'@TableData [dbo].[BulkUdt] READONLY',@aaa@qq.com
*/
}
#endregion
}
}本地数据库字段
效果展示
把这些字段信息 存到自己的 本地数据库
以后有需要的时候 直接条件查询.
就可以查看到这个字段的所有信息了.
大家也可以ctrl +A 全选后 鼠标右键. 有 “将结果另存为” .
保存的类型就选择 .csv 就可以用 excel 打开. 或者 全选后 选择 ” 连同标题一起复制 “,
然后粘贴到 excel. 以后在excel中也可以 搜索.
上一篇： zappix的api接口