Search Database Objects on specified instance

最新推荐文章于 2024-02-07 22:09:49 发布

zhudonhua

最新推荐文章于 2024-02-07 22:09:49 发布

阅读量241

点赞数

分类专栏： T-SQL 脚本中心文章标签： search database table object security insert

本文链接：https://blog.csdn.net/zhudonhua/article/details/3014928

版权

T-SQL 脚本中心专栏收录该内容

0 篇文章 0 订阅

订阅专栏

@echo off
echo Object Name ?
set /p obj=

echo set nocount on                          > ./tmp.sql
echo if exists(SELECT 1 FROM tempdb.dbo.sysobjects                    >> ./tmp.sql
echo           WHERE name = '##db' AND type in (N'U'))          >> ./tmp.sql
echo    drop table ##db                                          >> ./tmp.sql

echo if exists(SELECT 1 FROM tempdb.dbo.sysobjects                    >> ./tmp.sql
echo           WHERE name = '##tmp' AND type in (N'U'))        >> ./tmp.sql
echo    drop table ##tmp                                         >> ./tmp.sql

echo create table ##tmp                                >> ./tmp.sql
echo (                                    >> ./tmp.sql
echo    dbname sysname,                                          >> ./tmp.sql
echo    objname sysname                                         >> ./tmp.sql
echo )                                                  >> ./tmp.sql

echo declare @dbname sysname                     >> ./tmp.sql
echo declare @sqlcmd sysname                     >> ./tmp.sql
echo select name into ##db from master..sysdatabases                  >> ./tmp.sql
echo     where lower(name) not in ('aud','security') order by dbid >> ./tmp.sql
echo while exists(select 1 from ##db)                                 >> ./tmp.sql
echo begin                                                            >> ./tmp.sql
echo       select top 1 @dbname=name from ##db                        >> ./tmp.sql
echo set @sqlcmd=N'insert into ##tmp select ''' + @dbname       >> ./tmp.sql
echo             +''', name from '+ @dbname+ '..sysobjects'    >> ./tmp.sql
echo          +' where name like ''%%%obj%%%'''            >> ./tmp.sql
echo    exec(@sqlcmd)                         >> ./tmp.sql
echo       delete from ##db where name=@dbname                        >> ./tmp.sql
echo end                                                              >> ./tmp.sql

echo select dbname DB_Name, objname Object_Name from ##tmp       >> ./tmp.sql

echo if exists(SELECT 1 FROM tempdb.dbo.sysobjects                    >> ./tmp.sql
echo           WHERE name = '##db' AND type in (N'U'))          >> ./tmp.sql
echo    drop table ##db                                          >> ./tmp.sql
echo set nocount off                         >> ./tmp.sql

echo Server Name which you want to search objects on ?
set /p srvname=
echo DBA Account?
set /p user=

sqlcmd -S%srvname% -U%user% -y30 -Y30 -v SQLCMDEDITOR="uedit32.exe" -i"./tmp.sql"
del ./tmp.sql
pause