Sql как получить список всех баз

Как получить список баз данных в Microsoft SQL Server с помощью T-SQL?

В этой короткой заметке я покажу вам два способа получить список баз данных в T-SQL. в Microsoft SQL Server. Первый способ — использовать системное представление sys.databases, второй — использовать системную хранимую процедуру sp_helpdb. части я дам некоторые рекомендации.

Получение списка баз данных с помощью представления sys.databases

Теперь мы напишем SQL-запрос, используя show sys.databases , который покажет нам список баз данных в экземпляре Microsoft SQL Server.

На мой взгляд, этот метод более подходит, потому что представление sys.databases показывает подробную информацию о базе данных параметры, которые могут быть очень полезными.

В этом случае, например, мы покажем следующие параметры базы данных:

• Идентификатор базы данных;
• Имя базы данных;
• Дата создания базы данных;
• Состояние базы данных;
• Уровень совместимости;
• Модель восстановления.

Более подробное представление всех параметров, возвращаемых представлением sys.databases, можно найти в официальной документации по Transact-SQL — sys.databases System View

Список используемых баз данных sp_helpdb

Сохраненный sp_helpdb выводит меньше информации о параметрах базы данных, но отображает другую полезную информацию, например размер базы данных. Этот метод также предпочтителен, если клиентскому приложению требуется список баз данных, то есть лучше вызывать хранимую процедуру с клиента, чем отправлять запрос SQL SELECT.

Процедура sp_helpdb возвращает следующие данные:

• name — Имя базы данных;
• db_size: общий размер базы данных;
• owner — владелец базы данных;
• dbid — Идентификатор базы данных;
• created: дата создания базы данных; Состояние
• — список значений параметров базы данных, разделенных запятыми. Параметры представлены в формате «Параметр=Значение» , в случае логических параметров отображаются только имена разрешенных параметров;
• compatibility_level — Уровень совместимости.

Теперь вы знаете, как перечислять базы данных на Microsoft SQL Server. Если вы начинающий программист и не имеете базовых знаний SQL, то рекомендую вам прочитать книгу » The SQL Code » — -это руководство для самостоятельного изучения этого языка . Я написал книгу, в которой очень подробно рассказываю о языке SQL.

Примечание! Для полного изучения SQL и T-SQL я рекомендую пройти наши онлайн-курсы по T-SQL, в которых используется методология последовательного обучения, особенно для начинающих.

Источник

sp_databases (Transact-SQL)

Используется c: SQL Server (все поддерживаемые версии)

Список баз данных, которые находятся на экземпляре SQL Server или к которым можно получить доступ через шлюз базы данных.

Синтаксические обозначения в Transact-SQL

Синтаксис

Значения кодов возврата

Наборы результатов

Имя столбца	Тип данных	Описание
DATABASE_NAME	имя системы	имя базы данных. В ядре базы данных этот столбец представляет имя базы данных, хранящейся в представлении каталога sys.databases .
DATABASE_SIZE	int	Размер базы данных в килобайтах.
ПРИМЕЧАНИЕ	varchar (254)	Для ядра базы данных это поле всегда возвращает НУЛЕВОЙ.

Комментарии

Возвращенные имена баз данных можно использовать в качестве параметров в команде USE для изменения контекста текущей базы данных.

DATABASE_SIZE возвращает значение NULL для баз данных размером более 2,15 ТБ.

sp_databases не имеет эквивалента Open Database Connectivity (ODBC).

Разрешения

Требуется разрешение СОЗДАТЬ БАЗУ ДАННЫХ, ИЗМЕНИТЬ ЛЮБУЮ БАЗУ ДАННЫХ или ПРОСМОТР ЛЮБОГО ОПРЕДЕЛЕНИЯ; кроме того, должны быть разрешения на доступ к базе данных. В разрешении VIEW ANY DEFINITION нельзя отказать.

Примеры

В следующем примере показано выполнение процедуры sp_databases.

Источник

Как составить список всех баз данных в MySQL

При управлении серверами баз данных MySQL одной из наиболее распространенных задач, которые вам необходимо выполнить, является ознакомление со средой. Это включает в себя такие задачи, как перечисление баз данных, находящихся на сервере, отображение таблиц для определенной базы данных или получение информации об учетных записях пользователей и их разрешениях.

В этом руководстве объясняется, как вывести список всех баз данных на сервер MySQL или MariaDB. через командную строку.

Показать базы данных MySQL

Самый распространенный способ получить список баз данных MySQL — использовать клиент MySQL mysql для подключения к серверу MySQL и выполнить команду SHOW DATABASES.

Получите доступ к серверу MySQL с помощью следующей команды и при появлении запроса введите пароль пользователя MySQL:

В оболочке MySQL выполните следующую команду:

команда будет вывести список всех баз данных, для которых пользователю были предоставлены какие-либо разрешения. Вывод будет примерно таким:

Другая команда, которую вы можете использовать для вывода списка баз данных с помощью SHOW SCHEMES, это SHOW SCHEMES, что является синонимом SHOW DATABASES:

Вывод будет таким же, как при использовании команды SHOW DATABASES:

Просмотреть все базы данных MySQL

Чтобы просмотреть список всех баз данных на сервере MySQL, вы должны войти в систему как пользователь с доступом ко всем базам данных, по умолчанию пользователь root или MySQL, установите глобальное разрешение SHOW DATABASES.

Войдите в систему как пользователь root MySQL:

Запустите SHOW DATABASES:

Список всех баз данных на сервере MySQL:

Фильтрация вывода

Предложение LIKE может использоваться для фильтрации вывода команды SHOW DATABASES в соответствии с заданным шаблоном.

Например, следующее Команда вернет все базы данных, имена которых начинаются с «open»:

Если вы хотите выполнить более сложный поиск, обратитесь к таблице схемы базы данных information_schema, которая содержит информацию обо всех базах данных.

Следующая команда даст вам список всех баз данных, начинающихся с «open» или «word»:

Показать базы данных MySQL из командной строки

Чтобы получить список баз данных без входа в оболочку MySQL , вы можете использовать команду mysql с параметром -e, что означает запуск, или mysqlshow, которая будет отображать информацию о базах данных и таблицах.

Это особенно полезно, когда вы хотите работать с базами данных MySQL с помощью сценариев оболочки. .

Запустите в терминале следующую команду, чтобы отобразить список всех баз данных:

Вот пример использования команды mysqlshow:

Вывод будет идентичен вывод предыдущей команды.

Если вы хотите отфильтровать вывод, вы можете использовать команду grep.

Выводы

Вы узнали, как получить список все базы данных на вашем сервере Мой SQL.

Не стесняйтесь оставлять комментарии, если у вас есть какие-либо вопросы.

Ресурс

Исследуйте базы данных с помощью T -SQL

Как консультант по оптимизации производительности SQL Server и администратор базы данных в Ambient Consulting, я часто сталкиваюсь с необходимостью анализа узких мест производительности в экземплярах SQL Server, которые я вижу. или впервые в жизни. Это может быть сложной задачей. Как правило, большинство компаний не имеют документации в своих базах данных. А если и есть, то либо устаревшее, либо его поиск занимает несколько дней.

В этой статье я поделюсь базовым набором скриптов, которые извлекают информацию из метаданных с помощью системных функций, хранимых процедур, таблиц, dmv . Вместе они раскроют все секреты баз данных в нужном экземпляре: его размер, расположение файла, его структуру, включая столбцы, типы данных, значения по умолчанию, ключи и индексы.

Если вы когда-либо пытались получить любую из этих сведений с помощью графического интерфейса, я думаю, вы будете приятно удивлены объемом информации, которую эти сценарии предоставляют немедленно.

Как и в случае с любым другим сценарием, сначала протестируйте их в тестовой среде, прежде чем запускать. в производстве. Я бы порекомендовал вам запускать их на тестовых сайтах MS, таких как AdventureWorks или в пабах.

Хватит разговоров, позвольте мне показать вам сценарии!

Информация о сервере

Начнем с запросов, предоставляющих информацию о ваших серверах.

Основная информация

Сначала несколько @@Simple , чтобы предоставить нам некоторую информацию.

Как долго работает ваш SQL Server с момента последней перезагрузки? Обратите внимание, что системная база данных tempdb создается заново при каждом перезапуске SQL Server. Это метод определения времени последнего перезапуска сервера.

Связанные серверы

Связанные серверы — это соединения, позволяющие SQL Server получать доступ к другим серверам данных. Распределенные запросы могут выполняться на разных связанных серверах. Полезно знать, является ли ваш сервер базы данных автономным или подключен к другим серверам.

Список всех баз данных

Во-первых, давайте покажем список всех баз данных на сервере. Помните, что на каждом сервере есть четыре или пять системных баз данных (master, model, msdb, tempdb и дистрибутив, если вы используете репликацию). Вы можете исключить эти базы данных из будущих запросов. Очень легко получить список баз данных в SSMS, но эти запросы будут нашими строительными блоками для более сложных запросов.

Есть несколько способов получить список всех баз данных в T-SQL, и ниже вы увидите некоторые из них. Каждый метод возвращает похожий результат, но с некоторыми отличиями.

Последнее резервное копирование?

Стоп! Любой хороший администратор базы данных должен знать, есть ли у вас свежая резервная копия, прежде чем продолжить.

Лучше всего, если вы сразу узнаете путь к файлу самой последней резервной копии.

Активный пользователь соединения

Было бы неплохо узнать, какие базы данных используются в настоящее время, особенно если они будут решать проблемы с производительностью.

Примечание переводчика : это будет работать только на SQL Server 2012 и более поздних версиях. , в предыдущих выпусках столбец database_id отсутствовал в dmv sys.dm_exec_sessions. Чтобы узнать, над какими пользователями базы данных работают в данный момент, вы можете использовать sp_who.

Подробнее о базах данных

Давайте рассмотрим подробнее и посмотрим, как мы можем собирать информацию об объектах во всех их базах данных. базы данных с использованием различных представлений каталога и dmv. Большинство вопросов, перечисленных в этом разделе, относятся к одну базу данных, поэтому обязательно выберите нужную базу данных в SSMS или используйте команду базы данных. Также помните, что вы всегда можете увидеть, в каком контексте базы данных будет выполняться запрос, используя select db_name().

Системная таблица sys.objects является одним из ключей к сбору информации об объектах, составляющих ее данные. model.

Ниже приведен список типов объектов, о которых мы можем узнать (см. документацию по sys.objects в MSDN)

Другое представление каталога, такое как sys. таблиц и sys.views, получить доступ к sys.objects и предоставить информацию о конкретном типе объекта. С помощью этих представлений и функции OBJECTPROPERTY мы можем получить большой объем информации о каждом из объектов, составляющих нашу схему базы данных.

Расположение файлов базы данных

Физическое расположение выбранных База данных, включая основной файл данных (mdf) и файл журнала транзакций (ldf), может быть получена с помощью этих запросов.

Таблицы

Обозреватель объектов в SSMS, конечно же, отображает полный список таблиц. в выбранной базе данных, но некоторую информацию получить с помощью графического интерфейса сложнее, чем с помощью скриптов. Стандарт ANSI требует представления SCHEME_INFO, но они не предоставляют информацию о нестандартных объектах (таких как триггеры, расширенные процедуры и т. д.), поэтому лучше всего использовать представление каталога SQL Server.

Число записей в таблице

Если вы ничего не знаете о таблице, то все таблицы одинаково важны. Чем больше вы узнаете о таблицах, тем больше вы делите их на условно более важные и условно менее важные. важный. Как правило, таблицы с большим количеством записей, скорее всего, окажут значительное влияние на производительность.

В SSMS мы можем щелкнуть правой кнопкой мыши любую таблицу, открыть свойства на вкладке «Хранилище» и просмотреть количество записей в таблице.

Вручную собрать эту информацию обо всех таблицах достаточно сложно. Опять же, если мы напишем SELECT COUNT(*) FROM TABLENAME для каждой таблицы, нам придется писать много.

Гораздо удобнее использовать T-SQL для генерации скрипта. Следующий сценарий сгенерирует набор операторов T-SQL для получения количества строк в каждой таблице в текущей базе данных. Просто запустите его, скопируйте результат в новое окно и запустите.

Примечание переводчика : у меня запрос не сработал, она добавила схему к имени таблицы.

sp_msForEachTable

Sp_msforeachtable — это недокументированная функция, которая «путешествует» по всем таблицам базы данных и выполняет запрос, заменяя ‘?’ с именем текущей таблицы. Существует также аналогичная функция sp_msforeachdb, которая работает на уровне базы данных.

Существует несколько известных проблем с этой недокументированной функцией, например использование специальных символов в именах объектов. Эти. если имя таблицы или базы данных содержит символ «-», приведенная ниже хранимая процедура завершится ошибкой.

Самый быстрый способ получить количество записей — использовать кластеризованный индекс

Все вышеперечисленное используется Методы COUNT(*), которые работают медленно, если в таблице более 500 000 записей.

Самый быстрый способ получить количество записей в таблице — получить количество записей в кластеризованном индексе или куче. Обратите внимание, что хотя этот способ и самый быстрый, MS говорит, что информация о количестве записей индекса и фактическом количестве строк в таблице может не совпадать, потому что для обновления информации требуется время. В большинстве случаев эти значения одинаковы или очень-очень близки и скоро станут такими же.

Находит кучи (таблицы без кластеризованных индексов)

Работа с кучами похожа на работу с плоский файл вместо базы данных. Если вы хотите гарантированно получить полный контроль над таблицей для любого запроса, используйте кучи. Обычно я рекомендую добавлять первичный ключ ко всем таблицам в куче.

Работа с активностью таблиц

При работе над оптимизацией производительности очень важно знать, какие таблицы активно считываются и на которые активно пишут. Ранее мы выяснили, сколько записей содержится в наших таблицах, теперь давайте посмотрим, как часто они записываются и читаются.

Обратите внимание, что эта информация dmv очищается каждый раз при перезапуске SQL Server. Чем дольше работает сервер, тем достовернее будет статистика. Я чувствую себя намного увереннее со статистикой за 30 дней, чем со статистикой за 1 неделю.

Гораздо более продвинутая версия этого запроса представляет собой курсор, который собирает информацию обо всех таблицах во всех базах данных на сервере. Я вообще не фанат курсоров из-за их низкой производительности, но обход различных баз данных приносит им большую пользу.

Примечание переводчика : Курсор, который победил т работать, если вы перечисляете базы данных со статусом, отличным от ONLINE.

Представления

Представления — это, условно говоря, запросы, хранящиеся в базе данных. Вы можете думать о них как о виртуальных столах. Данные не хранятся в представлениях, но мы обращаемся к ним в наших запросах так же, как к таблицам.

В SQL Server в некоторых случаях мы можем обновлять данные с помощью представления. Чтобы получить представление только для чтения, вы можете использовать SELECT OTHER при его создании. Данные «во всем» представлении могут быть изменены только в том случае, если каждая строка в представлении соответствует одной строке в «базовой» таблице. Любое представление, которое не соответствует этим критериям, т. е. построено на нескольких таблицах или с использованием группировок, агрегаций и вычислений, будет доступно только для чтения.

Синонимы

Несколько раз в своей карьере я обнаруживал, что в ситуации, когда я не мог понять, к какой таблице относится запрос. Представьте себе простой клиентский запрос SELECT * FROM. Я ищу таблицу Customer, но не могу ее найти. Хорошо, я думаю, что это должно быть представление, я ищу представление под названием «Клиент» и до сих пор не могу его найти. Может я ошибся в базе данных? В результате Customer является синонимом клиентов, а таблица фактически называется Customer. Отдел маркетинга хотел назвать эту таблицу «Клиент», поэтому был создан синоним. К счастью, синонимы используются редко, но судебный процесс может быть сложным, если вы не готовы.

Хранимые процедуры

Хранимая процедура – это группа сценариев, которые объединяются в единый план реализации. Мы можем использовать представления каталога, чтобы определить, какие CP создаются, какие действия они выполняют и с какими таблицами.

Добавляя простое условие WHERE, мы можем получить информацию только о тех хранимых процедурах, которые, например, , выполнять операции INSERT.

Слегка измененное состояние в KDE, мы можем собирать информацию о HP, которые обновляют, удаляют или обращаются к определенным таблицам.

Функции

Функции сохраняются в SQL Server принимать некоторые параметры и выполнять определенные действия или вычисления, а затем возвращать результат.

Триггеры

Триггер — это что-то вроде хранимой процедуры, которая выполняется в ответ на определенные действия на таблица, которой принадлежит этот триггер. Например, мы можем создать триггеры INSERT, UPDATE и DELETE.

Ограничения CHECK

Ограничения CHECK — это хороший способ реализовать бизнес-логику в базе данных. Например, некоторые поля должны быть положительными или отрицательными, или дата в одном столбце должна быть позже даты в другом.

Глубоко в модели данных

Раньше мы использовали последовательности команд что дало нам представление «более высокого уровня» об объектах, составляющих нашу базу данных. Иногда нам нужно получить больше данных о таблице, включая столбцы, их типы данных, какие значения по умолчанию установлены, какие ключи и индексы существуют (или должны существовать) и т. д.

Запросы, перечисленные в этом Раздел предоставляет средства для почти обратного анализа существующей модели данных.

Столбцы

Следующий сценарий описывает таблицы и столбцы во всей базе данных. Результат этого запроса можно скопировать в Excel, где можно установить и отсортировать фильтры, а также понять типы данных, используемые в базе данных. Тот же путь, обратите внимание на столбцы с одинаковыми именами, но разными типами данных.

По умолчанию

Значение по умолчанию — это значение, которое будет сохранено, если для столбца во вставке не задано значение. . Get_date() часто устанавливается для столбцов, в которых хранятся даты. Значения по умолчанию также используются для аудита: system_user вставляется для указания учетной записи пользователя, выполнившего определенное действие. на основе значений других столбцов в таблице.

Столбцы идентификаторов

Столбцы идентификаторов автоматически заполняются системой уникальными значениями. Обычно используется для хранения порядкового номера записи в таблице.

Ключи и индексы

Как я уже писал, первичный ключ и соответствующий ему индекс в таблице — это один из лучшие практики. . Еще одна лучшая практика заключается в том, что внешние ключи также должны иметь встроенный индекс для столбцов, включенных во внешний ключ. Индексы, построенные «по внешним ключам», отлично подходят для соединения таблиц. Эти индексы также хороши для производительности при удалении записей.

Какие индексы у нас есть?

Скрипт для загрузки всех индексов во все таблицы в текущей базе данных.

Что? индексы отсутствуют?

На основе ранее выполненных запросов SQL Server предоставляет информацию о отсутствующие индексы в базе данных. Создание может повысить производительность.

Не добавляйте эти индексы вслепую. Я бы подумал о каждом из предложенных индексов. Например, использование включенных столбцов может привести к резкому увеличению объема.

Внешние ключи

Внешние ключи определяют отношения между таблицами и используются для управления ссылочной целостностью. На диаграмме отношений сущностей линии между таблицами представляют внешние ключи.

Отсутствующие индексы внешних ключей

Как я уже сказал, желательно иметь индекс, построенный на столбцах, включенных во внешние ключи. ключ. Это значительно ускоряет объединение таблиц, которые в любом случае обычно объединяются с помощью внешнего ключа. Эти индексы также значительно ускоряют операции удаления. Если такого индекса не существует, SQL Server будет сканировать связанную таблицу каждый раз, когда запись удаляется из «первой» таблицы.

Зависимости

Зависит… Я уверен, что вы слышал термин раньше. Я расскажу о трех различных методах «реверс-инжиниринга» зависимостей базы данных. Первый способ заключается в использовании хранимой процедуры sp_msdependencies. Второй — это системные таблицы, связанные с внешними ключами. Третий метод заключается в использовании CTE.

sp_ms dependencies

Sp_msdependencies — это недокументированная хранимая процедура, которая может быть очень полезна для анализа зависимостей сложных таблиц.

Да , перечисляем все зависимости с помощью sp_msdependencies, получаем четыре столбца: Type, ObjName, Owner(Schema), Sequence.

Обратите внимание на порядковый номер (Sequence) — он начинается с 1 и постепенно увеличивается. Последовательность — это «порядковый номер» зависимости.

Я использовал этот метод несколько раз, когда мне нужно было заархивировать или удалить очень большую базу данных. Если вы знаете зависимости таблиц, у вас есть план: в каком порядке вам нужно архивировать или удалять данные. Начните с таблицы с наибольшим значением в столбце «Последовательность» и работайте в обратном порядке, от наибольшего к наименьшему. Таблицы с одинаковым значением последовательности могут быть удалены одновременно. Этот метод не нарушает никаких ограничений внешнего ключа и позволяет перемещать/удалять записи без обратимого удаления или перестроения ограничений (ограничений).

В SSMS да Если щелкнуть правой кнопкой мыши имя таблицы, вы можете выбрать «Показать зависимости» и «Объекты, которые зависят от TABLENAME»:

Мы также можем получить эту информацию следующим образом :

Если в SSMS в обозревателе зависимостей выбрать «Объекты, зависящие от TABLENAME», а затем развернуть все уровни, мы увидим следующее:

sp_msdependencies возвращает ту же информацию.

Кроме того, в SSMS мы можем видеть, от каких объектов зависит выбранная таблица on.

Другой запрос с использованием msdependencies возвращает ту же информацию.

Если вы хотите получить список зависимостей таблицы, вы можете использовать временную таблицу для фильтрации зависимостей по типу. atabase — это запросы к представлениям системного каталога, связанные с внешние ключи.

Использование CTE

Третий метод получения иерархии зависимостей — использование рекурсивного CTE.

Заключение

Итак, через час или два, вы можете получить хорошее представление о внутренностях любой базы данных, используя описанные выше методы «обратного проектирования».

Рабочее примечание Adductor : Все запросы в тексте (кроме упомянутый в тексте) будет работать в SQL Server 2005 SP3 и более поздних версиях. Текст достаточно объемный, я старалась как можно лучше его прочитать и найти свои ошибки (стилистические, синтаксические, смысловые и другие), но, возможно, что-то не заметила, пожалуйста, напишите мне в личку, если вас что-то задело. глаз.

Источник